1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    is_int,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42
  43class _Expression(type):
  44    def __new__(cls, clsname, bases, attrs):
  45        klass = super().__new__(cls, clsname, bases, attrs)
  46
  47        # When an Expression class is created, its key is automatically set to be
  48        # the lowercase version of the class' name.
  49        klass.key = clsname.lower()
  50
  51        # This is so that docstrings are not inherited in pdoc
  52        klass.__doc__ = klass.__doc__ or ""
  53
  54        return klass
  55
  56
  57SQLGLOT_META = "sqlglot.meta"
  58TABLE_PARTS = ("this", "db", "catalog")
  59
  60
  61class Expression(metaclass=_Expression):
  62    """
  63    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  64    context, such as its child expressions, their names (arg keys), and whether a given child expression
  65    is optional or not.
  66
  67    Attributes:
  68        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  69            and representing expressions as strings.
  70        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  71            arg keys to booleans that indicate whether the corresponding args are optional.
  72        parent: a reference to the parent expression (or None, in case of root expressions).
  73        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  74            uses to refer to it.
  75        comments: a list of comments that are associated with a given expression. This is used in
  76            order to preserve comments when transpiling SQL code.
  77        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  78            optimizer, in order to enable some transformations that require type information.
  79        meta: a dictionary that can be used to store useful metadata for a given expression.
  80
  81    Example:
  82        >>> class Foo(Expression):
  83        ...     arg_types = {"this": True, "expression": False}
  84
  85        The above definition informs us that Foo is an Expression that requires an argument called
  86        "this" and may also optionally receive an argument called "expression".
  87
  88    Args:
  89        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  90    """
  91
  92    key = "expression"
  93    arg_types = {"this": True}
  94    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  95
  96    def __init__(self, **args: t.Any):
  97        self.args: t.Dict[str, t.Any] = args
  98        self.parent: t.Optional[Expression] = None
  99        self.arg_key: t.Optional[str] = None
 100        self.comments: t.Optional[t.List[str]] = None
 101        self._type: t.Optional[DataType] = None
 102        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 103        self._hash: t.Optional[int] = None
 104
 105        for arg_key, value in self.args.items():
 106            self._set_parent(arg_key, value)
 107
 108    def __eq__(self, other) -> bool:
 109        return type(self) is type(other) and hash(self) == hash(other)
 110
 111    @property
 112    def hashable_args(self) -> t.Any:
 113        return frozenset(
 114            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 115            for k, v in self.args.items()
 116            if not (v is None or v is False or (type(v) is list and not v))
 117        )
 118
 119    def __hash__(self) -> int:
 120        if self._hash is not None:
 121            return self._hash
 122
 123        return hash((self.__class__, self.hashable_args))
 124
 125    @property
 126    def this(self) -> t.Any:
 127        """
 128        Retrieves the argument with key "this".
 129        """
 130        return self.args.get("this")
 131
 132    @property
 133    def expression(self) -> t.Any:
 134        """
 135        Retrieves the argument with key "expression".
 136        """
 137        return self.args.get("expression")
 138
 139    @property
 140    def expressions(self) -> t.List[t.Any]:
 141        """
 142        Retrieves the argument with key "expressions".
 143        """
 144        return self.args.get("expressions") or []
 145
 146    def text(self, key) -> str:
 147        """
 148        Returns a textual representation of the argument corresponding to "key". This can only be used
 149        for args that are strings or leaf Expression instances, such as identifiers and literals.
 150        """
 151        field = self.args.get(key)
 152        if isinstance(field, str):
 153            return field
 154        if isinstance(field, (Identifier, Literal, Var)):
 155            return field.this
 156        if isinstance(field, (Star, Null)):
 157            return field.name
 158        return ""
 159
 160    @property
 161    def is_string(self) -> bool:
 162        """
 163        Checks whether a Literal expression is a string.
 164        """
 165        return isinstance(self, Literal) and self.args["is_string"]
 166
 167    @property
 168    def is_number(self) -> bool:
 169        """
 170        Checks whether a Literal expression is a number.
 171        """
 172        return isinstance(self, Literal) and not self.args["is_string"]
 173
 174    @property
 175    def is_int(self) -> bool:
 176        """
 177        Checks whether a Literal expression is an integer.
 178        """
 179        return self.is_number and is_int(self.name)
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    def is_type(self, *dtypes) -> bool:
 239        return self.type is not None and self.type.is_type(*dtypes)
 240
 241    def is_leaf(self) -> bool:
 242        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return ancestor  # type: ignore
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes whose parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and type(n) is not self.__class__):
 492            if type(node) is not self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return _to_s(self)
 500
 501    def to_s(self) -> str:
 502        """
 503        Same as __repr__, but includes additional information which can be useful
 504        for debugging, like empty or missing args and the AST nodes' object IDs.
 505        """
 506        return _to_s(self, verbose=True)
 507
 508    def sql(self, dialect: DialectType = None, **opts) -> str:
 509        """
 510        Returns SQL string representation of this tree.
 511
 512        Args:
 513            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 514            opts: other `sqlglot.generator.Generator` options.
 515
 516        Returns:
 517            The SQL string.
 518        """
 519        from sqlglot.dialects import Dialect
 520
 521        return Dialect.get_or_raise(dialect).generate(self, **opts)
 522
 523    def transform(self, fun, *args, copy=True, **kwargs):
 524        """
 525        Recursively visits all tree nodes (excluding already transformed ones)
 526        and applies the given transformation function to each node.
 527
 528        Args:
 529            fun (function): a function which takes a node as an argument and returns a
 530                new transformed node or the same node without modifications. If the function
 531                returns None, then the corresponding node will be removed from the syntax tree.
 532            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 533                modified in place.
 534
 535        Returns:
 536            The transformed tree.
 537        """
 538        node = self.copy() if copy else self
 539        new_node = fun(node, *args, **kwargs)
 540
 541        if new_node is None or not isinstance(new_node, Expression):
 542            return new_node
 543        if new_node is not node:
 544            new_node.parent = node.parent
 545            return new_node
 546
 547        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 548        return new_node
 549
 550    @t.overload
 551    def replace(self, expression: E) -> E:
 552        ...
 553
 554    @t.overload
 555    def replace(self, expression: None) -> None:
 556        ...
 557
 558    def replace(self, expression):
 559        """
 560        Swap out this expression with a new expression.
 561
 562        For example::
 563
 564            >>> tree = Select().select("x").from_("tbl")
 565            >>> tree.find(Column).replace(column("y"))
 566            Column(
 567              this=Identifier(this=y, quoted=False))
 568            >>> tree.sql()
 569            'SELECT y FROM tbl'
 570
 571        Args:
 572            expression: new node
 573
 574        Returns:
 575            The new expression or expressions.
 576        """
 577        if not self.parent:
 578            return expression
 579
 580        parent = self.parent
 581        self.parent = None
 582
 583        replace_children(parent, lambda child: expression if child is self else child)
 584        return expression
 585
 586    def pop(self: E) -> E:
 587        """
 588        Remove this expression from its AST.
 589
 590        Returns:
 591            The popped expression.
 592        """
 593        self.replace(None)
 594        return self
 595
 596    def assert_is(self, type_: t.Type[E]) -> E:
 597        """
 598        Assert that this `Expression` is an instance of `type_`.
 599
 600        If it is NOT an instance of `type_`, this raises an assertion error.
 601        Otherwise, this returns this expression.
 602
 603        Examples:
 604            This is useful for type security in chained expressions:
 605
 606            >>> import sqlglot
 607            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 608            'SELECT x, z FROM y'
 609        """
 610        if not isinstance(self, type_):
 611            raise AssertionError(f"{self} is not {type_}.")
 612        return self
 613
 614    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 615        """
 616        Checks if this expression is valid (e.g. all mandatory args are set).
 617
 618        Args:
 619            args: a sequence of values that were used to instantiate a Func expression. This is used
 620                to check that the provided arguments don't exceed the function argument limit.
 621
 622        Returns:
 623            A list of error messages for all possible errors that were found.
 624        """
 625        errors: t.List[str] = []
 626
 627        for k in self.args:
 628            if k not in self.arg_types:
 629                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 630        for k, mandatory in self.arg_types.items():
 631            v = self.args.get(k)
 632            if mandatory and (v is None or (isinstance(v, list) and not v)):
 633                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 634
 635        if (
 636            args
 637            and isinstance(self, Func)
 638            and len(args) > len(self.arg_types)
 639            and not self.is_var_len_args
 640        ):
 641            errors.append(
 642                f"The number of provided arguments ({len(args)}) is greater than "
 643                f"the maximum number of supported arguments ({len(self.arg_types)})"
 644            )
 645
 646        return errors
 647
 648    def dump(self):
 649        """
 650        Dump this Expression to a JSON-serializable dict.
 651        """
 652        from sqlglot.serde import dump
 653
 654        return dump(self)
 655
 656    @classmethod
 657    def load(cls, obj):
 658        """
 659        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 660        """
 661        from sqlglot.serde import load
 662
 663        return load(obj)
 664
 665    def and_(
 666        self,
 667        *expressions: t.Optional[ExpOrStr],
 668        dialect: DialectType = None,
 669        copy: bool = True,
 670        **opts,
 671    ) -> Condition:
 672        """
 673        AND this condition with one or multiple expressions.
 674
 675        Example:
 676            >>> condition("x=1").and_("y=1").sql()
 677            'x = 1 AND y = 1'
 678
 679        Args:
 680            *expressions: the SQL code strings to parse.
 681                If an `Expression` instance is passed, it will be used as-is.
 682            dialect: the dialect used to parse the input expression.
 683            copy: whether to copy the involved expressions (only applies to Expressions).
 684            opts: other options to use to parse the input expressions.
 685
 686        Returns:
 687            The new And condition.
 688        """
 689        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 690
 691    def or_(
 692        self,
 693        *expressions: t.Optional[ExpOrStr],
 694        dialect: DialectType = None,
 695        copy: bool = True,
 696        **opts,
 697    ) -> Condition:
 698        """
 699        OR this condition with one or multiple expressions.
 700
 701        Example:
 702            >>> condition("x=1").or_("y=1").sql()
 703            'x = 1 OR y = 1'
 704
 705        Args:
 706            *expressions: the SQL code strings to parse.
 707                If an `Expression` instance is passed, it will be used as-is.
 708            dialect: the dialect used to parse the input expression.
 709            copy: whether to copy the involved expressions (only applies to Expressions).
 710            opts: other options to use to parse the input expressions.
 711
 712        Returns:
 713            The new Or condition.
 714        """
 715        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 716
 717    def not_(self, copy: bool = True):
 718        """
 719        Wrap this condition with NOT.
 720
 721        Example:
 722            >>> condition("x=1").not_().sql()
 723            'NOT x = 1'
 724
 725        Args:
 726            copy: whether to copy this object.
 727
 728        Returns:
 729            The new Not instance.
 730        """
 731        return not_(self, copy=copy)
 732
 733    def as_(
 734        self,
 735        alias: str | Identifier,
 736        quoted: t.Optional[bool] = None,
 737        dialect: DialectType = None,
 738        copy: bool = True,
 739        **opts,
 740    ) -> Alias:
 741        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 742
 743    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 744        this = self.copy()
 745        other = convert(other, copy=True)
 746        if not isinstance(this, klass) and not isinstance(other, klass):
 747            this = _wrap(this, Binary)
 748            other = _wrap(other, Binary)
 749        if reverse:
 750            return klass(this=other, expression=this)
 751        return klass(this=this, expression=other)
 752
 753    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 754        return Bracket(
 755            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 756        )
 757
 758    def __iter__(self) -> t.Iterator:
 759        if "expressions" in self.arg_types:
 760            return iter(self.args.get("expressions") or [])
 761        # We define this because __getitem__ converts Expression into an iterable, which is
 762        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 763        # See: https://peps.python.org/pep-0234/
 764        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 765
 766    def isin(
 767        self,
 768        *expressions: t.Any,
 769        query: t.Optional[ExpOrStr] = None,
 770        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 771        copy: bool = True,
 772        **opts,
 773    ) -> In:
 774        return In(
 775            this=maybe_copy(self, copy),
 776            expressions=[convert(e, copy=copy) for e in expressions],
 777            query=maybe_parse(query, copy=copy, **opts) if query else None,
 778            unnest=(
 779                Unnest(
 780                    expressions=[
 781                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 782                        for e in ensure_list(unnest)
 783                    ]
 784                )
 785                if unnest
 786                else None
 787            ),
 788        )
 789
 790    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 791        return Between(
 792            this=maybe_copy(self, copy),
 793            low=convert(low, copy=copy, **opts),
 794            high=convert(high, copy=copy, **opts),
 795        )
 796
 797    def is_(self, other: ExpOrStr) -> Is:
 798        return self._binop(Is, other)
 799
 800    def like(self, other: ExpOrStr) -> Like:
 801        return self._binop(Like, other)
 802
 803    def ilike(self, other: ExpOrStr) -> ILike:
 804        return self._binop(ILike, other)
 805
 806    def eq(self, other: t.Any) -> EQ:
 807        return self._binop(EQ, other)
 808
 809    def neq(self, other: t.Any) -> NEQ:
 810        return self._binop(NEQ, other)
 811
 812    def rlike(self, other: ExpOrStr) -> RegexpLike:
 813        return self._binop(RegexpLike, other)
 814
 815    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 816        div = self._binop(Div, other)
 817        div.args["typed"] = typed
 818        div.args["safe"] = safe
 819        return div
 820
 821    def desc(self, nulls_first: bool = False) -> Ordered:
 822        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Subqueryable) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Unionable(Expression):
 924    def union(
 925        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 926    ) -> Union:
 927        """
 928        Builds a UNION expression.
 929
 930        Example:
 931            >>> import sqlglot
 932            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 933            'SELECT * FROM foo UNION SELECT * FROM bla'
 934
 935        Args:
 936            expression: the SQL code string.
 937                If an `Expression` instance is passed, it will be used as-is.
 938            distinct: set the DISTINCT flag if and only if this is true.
 939            dialect: the dialect used to parse the input expression.
 940            opts: other options to use to parse the input expressions.
 941
 942        Returns:
 943            The new Union expression.
 944        """
 945        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 946
 947    def intersect(
 948        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 949    ) -> Unionable:
 950        """
 951        Builds an INTERSECT expression.
 952
 953        Example:
 954            >>> import sqlglot
 955            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 956            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 957
 958        Args:
 959            expression: the SQL code string.
 960                If an `Expression` instance is passed, it will be used as-is.
 961            distinct: set the DISTINCT flag if and only if this is true.
 962            dialect: the dialect used to parse the input expression.
 963            opts: other options to use to parse the input expressions.
 964
 965        Returns:
 966            The new Intersect expression.
 967        """
 968        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 969
 970    def except_(
 971        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 972    ) -> Unionable:
 973        """
 974        Builds an EXCEPT expression.
 975
 976        Example:
 977            >>> import sqlglot
 978            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 979            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 980
 981        Args:
 982            expression: the SQL code string.
 983                If an `Expression` instance is passed, it will be used as-is.
 984            distinct: set the DISTINCT flag if and only if this is true.
 985            dialect: the dialect used to parse the input expression.
 986            opts: other options to use to parse the input expressions.
 987
 988        Returns:
 989            The new Except expression.
 990        """
 991        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 992
 993
 994class UDTF(DerivedTable, Unionable):
 995    @property
 996    def selects(self) -> t.List[Expression]:
 997        alias = self.args.get("alias")
 998        return alias.columns if alias else []
 999
1000
1001class Cache(Expression):
1002    arg_types = {
1003        "this": True,
1004        "lazy": False,
1005        "options": False,
1006        "expression": False,
1007    }
1008
1009
1010class Uncache(Expression):
1011    arg_types = {"this": True, "exists": False}
1012
1013
1014class Refresh(Expression):
1015    pass
1016
1017
1018class DDL(Expression):
1019    @property
1020    def ctes(self):
1021        with_ = self.args.get("with")
1022        if not with_:
1023            return []
1024        return with_.expressions
1025
1026    @property
1027    def named_selects(self) -> t.List[str]:
1028        if isinstance(self.expression, Subqueryable):
1029            return self.expression.named_selects
1030        return []
1031
1032    @property
1033    def selects(self) -> t.List[Expression]:
1034        if isinstance(self.expression, Subqueryable):
1035            return self.expression.selects
1036        return []
1037
1038
1039class DML(Expression):
1040    def returning(
1041        self,
1042        expression: ExpOrStr,
1043        dialect: DialectType = None,
1044        copy: bool = True,
1045        **opts,
1046    ) -> DML:
1047        """
1048        Set the RETURNING expression. Not supported by all dialects.
1049
1050        Example:
1051            >>> delete("tbl").returning("*", dialect="postgres").sql()
1052            'DELETE FROM tbl RETURNING *'
1053
1054        Args:
1055            expression: the SQL code strings to parse.
1056                If an `Expression` instance is passed, it will be used as-is.
1057            dialect: the dialect used to parse the input expressions.
1058            copy: if `False`, modify this expression instance in-place.
1059            opts: other options to use to parse the input expressions.
1060
1061        Returns:
1062            Delete: the modified expression.
1063        """
1064        return _apply_builder(
1065            expression=expression,
1066            instance=self,
1067            arg="returning",
1068            prefix="RETURNING",
1069            dialect=dialect,
1070            copy=copy,
1071            into=Returning,
1072            **opts,
1073        )
1074
1075
1076class Create(DDL):
1077    arg_types = {
1078        "with": False,
1079        "this": True,
1080        "kind": True,
1081        "expression": False,
1082        "exists": False,
1083        "properties": False,
1084        "replace": False,
1085        "unique": False,
1086        "indexes": False,
1087        "no_schema_binding": False,
1088        "begin": False,
1089        "end": False,
1090        "clone": False,
1091    }
1092
1093    @property
1094    def kind(self) -> t.Optional[str]:
1095        kind = self.args.get("kind")
1096        return kind and kind.upper()
1097
1098
1099# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1100# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1101# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1102class Clone(Expression):
1103    arg_types = {"this": True, "shallow": False, "copy": False}
1104
1105
1106class Describe(Expression):
1107    arg_types = {"this": True, "extended": False, "kind": False, "expressions": False}
1108
1109
1110class Kill(Expression):
1111    arg_types = {"this": True, "kind": False}
1112
1113
1114class Pragma(Expression):
1115    pass
1116
1117
1118class Set(Expression):
1119    arg_types = {"expressions": False, "unset": False, "tag": False}
1120
1121
1122class Heredoc(Expression):
1123    arg_types = {"this": True, "tag": False}
1124
1125
1126class SetItem(Expression):
1127    arg_types = {
1128        "this": False,
1129        "expressions": False,
1130        "kind": False,
1131        "collate": False,  # MySQL SET NAMES statement
1132        "global": False,
1133    }
1134
1135
1136class Show(Expression):
1137    arg_types = {
1138        "this": True,
1139        "history": False,
1140        "terse": False,
1141        "target": False,
1142        "offset": False,
1143        "starts_with": False,
1144        "limit": False,
1145        "from": False,
1146        "like": False,
1147        "where": False,
1148        "db": False,
1149        "scope": False,
1150        "scope_kind": False,
1151        "full": False,
1152        "mutex": False,
1153        "query": False,
1154        "channel": False,
1155        "global": False,
1156        "log": False,
1157        "position": False,
1158        "types": False,
1159    }
1160
1161
1162class UserDefinedFunction(Expression):
1163    arg_types = {"this": True, "expressions": False, "wrapped": False}
1164
1165
1166class CharacterSet(Expression):
1167    arg_types = {"this": True, "default": False}
1168
1169
1170class With(Expression):
1171    arg_types = {"expressions": True, "recursive": False}
1172
1173    @property
1174    def recursive(self) -> bool:
1175        return bool(self.args.get("recursive"))
1176
1177
1178class WithinGroup(Expression):
1179    arg_types = {"this": True, "expression": False}
1180
1181
1182# clickhouse supports scalar ctes
1183# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1184class CTE(DerivedTable):
1185    arg_types = {"this": True, "alias": True, "scalar": False}
1186
1187
1188class TableAlias(Expression):
1189    arg_types = {"this": False, "columns": False}
1190
1191    @property
1192    def columns(self):
1193        return self.args.get("columns") or []
1194
1195
1196class BitString(Condition):
1197    pass
1198
1199
1200class HexString(Condition):
1201    pass
1202
1203
1204class ByteString(Condition):
1205    pass
1206
1207
1208class RawString(Condition):
1209    pass
1210
1211
1212class UnicodeString(Condition):
1213    arg_types = {"this": True, "escape": False}
1214
1215
1216class Column(Condition):
1217    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1218
1219    @property
1220    def table(self) -> str:
1221        return self.text("table")
1222
1223    @property
1224    def db(self) -> str:
1225        return self.text("db")
1226
1227    @property
1228    def catalog(self) -> str:
1229        return self.text("catalog")
1230
1231    @property
1232    def output_name(self) -> str:
1233        return self.name
1234
1235    @property
1236    def parts(self) -> t.List[Identifier]:
1237        """Return the parts of a column in order catalog, db, table, name."""
1238        return [
1239            t.cast(Identifier, self.args[part])
1240            for part in ("catalog", "db", "table", "this")
1241            if self.args.get(part)
1242        ]
1243
1244    def to_dot(self) -> Dot | Identifier:
1245        """Converts the column into a dot expression."""
1246        parts = self.parts
1247        parent = self.parent
1248
1249        while parent:
1250            if isinstance(parent, Dot):
1251                parts.append(parent.expression)
1252            parent = parent.parent
1253
1254        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1255
1256
1257class ColumnPosition(Expression):
1258    arg_types = {"this": False, "position": True}
1259
1260
1261class ColumnDef(Expression):
1262    arg_types = {
1263        "this": True,
1264        "kind": False,
1265        "constraints": False,
1266        "exists": False,
1267        "position": False,
1268    }
1269
1270    @property
1271    def constraints(self) -> t.List[ColumnConstraint]:
1272        return self.args.get("constraints") or []
1273
1274
1275class AlterColumn(Expression):
1276    arg_types = {
1277        "this": True,
1278        "dtype": False,
1279        "collate": False,
1280        "using": False,
1281        "default": False,
1282        "drop": False,
1283        "comment": False,
1284    }
1285
1286
1287class RenameColumn(Expression):
1288    arg_types = {"this": True, "to": True, "exists": False}
1289
1290
1291class RenameTable(Expression):
1292    pass
1293
1294
1295class SwapTable(Expression):
1296    pass
1297
1298
1299class Comment(Expression):
1300    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1301
1302
1303class Comprehension(Expression):
1304    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1305
1306
1307# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1308class MergeTreeTTLAction(Expression):
1309    arg_types = {
1310        "this": True,
1311        "delete": False,
1312        "recompress": False,
1313        "to_disk": False,
1314        "to_volume": False,
1315    }
1316
1317
1318# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1319class MergeTreeTTL(Expression):
1320    arg_types = {
1321        "expressions": True,
1322        "where": False,
1323        "group": False,
1324        "aggregates": False,
1325    }
1326
1327
1328# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1329class IndexConstraintOption(Expression):
1330    arg_types = {
1331        "key_block_size": False,
1332        "using": False,
1333        "parser": False,
1334        "comment": False,
1335        "visible": False,
1336        "engine_attr": False,
1337        "secondary_engine_attr": False,
1338    }
1339
1340
1341class ColumnConstraint(Expression):
1342    arg_types = {"this": False, "kind": True}
1343
1344    @property
1345    def kind(self) -> ColumnConstraintKind:
1346        return self.args["kind"]
1347
1348
1349class ColumnConstraintKind(Expression):
1350    pass
1351
1352
1353class AutoIncrementColumnConstraint(ColumnConstraintKind):
1354    pass
1355
1356
1357class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1358    arg_types = {"this": True, "expression": True}
1359
1360
1361class CaseSpecificColumnConstraint(ColumnConstraintKind):
1362    arg_types = {"not_": True}
1363
1364
1365class CharacterSetColumnConstraint(ColumnConstraintKind):
1366    arg_types = {"this": True}
1367
1368
1369class CheckColumnConstraint(ColumnConstraintKind):
1370    arg_types = {"this": True, "enforced": False}
1371
1372
1373class ClusteredColumnConstraint(ColumnConstraintKind):
1374    pass
1375
1376
1377class CollateColumnConstraint(ColumnConstraintKind):
1378    pass
1379
1380
1381class CommentColumnConstraint(ColumnConstraintKind):
1382    pass
1383
1384
1385class CompressColumnConstraint(ColumnConstraintKind):
1386    pass
1387
1388
1389class DateFormatColumnConstraint(ColumnConstraintKind):
1390    arg_types = {"this": True}
1391
1392
1393class DefaultColumnConstraint(ColumnConstraintKind):
1394    pass
1395
1396
1397class EncodeColumnConstraint(ColumnConstraintKind):
1398    pass
1399
1400
1401class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1402    # this: True -> ALWAYS, this: False -> BY DEFAULT
1403    arg_types = {
1404        "this": False,
1405        "expression": False,
1406        "on_null": False,
1407        "start": False,
1408        "increment": False,
1409        "minvalue": False,
1410        "maxvalue": False,
1411        "cycle": False,
1412    }
1413
1414
1415class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1416    arg_types = {"start": False, "hidden": False}
1417
1418
1419# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1420class IndexColumnConstraint(ColumnConstraintKind):
1421    arg_types = {
1422        "this": False,
1423        "schema": True,
1424        "kind": False,
1425        "index_type": False,
1426        "options": False,
1427    }
1428
1429
1430class InlineLengthColumnConstraint(ColumnConstraintKind):
1431    pass
1432
1433
1434class NonClusteredColumnConstraint(ColumnConstraintKind):
1435    pass
1436
1437
1438class NotForReplicationColumnConstraint(ColumnConstraintKind):
1439    arg_types = {}
1440
1441
1442class NotNullColumnConstraint(ColumnConstraintKind):
1443    arg_types = {"allow_null": False}
1444
1445
1446# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1447class OnUpdateColumnConstraint(ColumnConstraintKind):
1448    pass
1449
1450
1451# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1452class TransformColumnConstraint(ColumnConstraintKind):
1453    pass
1454
1455
1456class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1457    arg_types = {"desc": False}
1458
1459
1460class TitleColumnConstraint(ColumnConstraintKind):
1461    pass
1462
1463
1464class UniqueColumnConstraint(ColumnConstraintKind):
1465    arg_types = {"this": False, "index_type": False}
1466
1467
1468class UppercaseColumnConstraint(ColumnConstraintKind):
1469    arg_types: t.Dict[str, t.Any] = {}
1470
1471
1472class PathColumnConstraint(ColumnConstraintKind):
1473    pass
1474
1475
1476# computed column expression
1477# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1478class ComputedColumnConstraint(ColumnConstraintKind):
1479    arg_types = {"this": True, "persisted": False, "not_null": False}
1480
1481
1482class Constraint(Expression):
1483    arg_types = {"this": True, "expressions": True}
1484
1485
1486class Delete(DML):
1487    arg_types = {
1488        "with": False,
1489        "this": False,
1490        "using": False,
1491        "where": False,
1492        "returning": False,
1493        "limit": False,
1494        "tables": False,  # Multiple-Table Syntax (MySQL)
1495    }
1496
1497    def delete(
1498        self,
1499        table: ExpOrStr,
1500        dialect: DialectType = None,
1501        copy: bool = True,
1502        **opts,
1503    ) -> Delete:
1504        """
1505        Create a DELETE expression or replace the table on an existing DELETE expression.
1506
1507        Example:
1508            >>> delete("tbl").sql()
1509            'DELETE FROM tbl'
1510
1511        Args:
1512            table: the table from which to delete.
1513            dialect: the dialect used to parse the input expression.
1514            copy: if `False`, modify this expression instance in-place.
1515            opts: other options to use to parse the input expressions.
1516
1517        Returns:
1518            Delete: the modified expression.
1519        """
1520        return _apply_builder(
1521            expression=table,
1522            instance=self,
1523            arg="this",
1524            dialect=dialect,
1525            into=Table,
1526            copy=copy,
1527            **opts,
1528        )
1529
1530    def where(
1531        self,
1532        *expressions: t.Optional[ExpOrStr],
1533        append: bool = True,
1534        dialect: DialectType = None,
1535        copy: bool = True,
1536        **opts,
1537    ) -> Delete:
1538        """
1539        Append to or set the WHERE expressions.
1540
1541        Example:
1542            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1543            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1544
1545        Args:
1546            *expressions: the SQL code strings to parse.
1547                If an `Expression` instance is passed, it will be used as-is.
1548                Multiple expressions are combined with an AND operator.
1549            append: if `True`, AND the new expressions to any existing expression.
1550                Otherwise, this resets the expression.
1551            dialect: the dialect used to parse the input expressions.
1552            copy: if `False`, modify this expression instance in-place.
1553            opts: other options to use to parse the input expressions.
1554
1555        Returns:
1556            Delete: the modified expression.
1557        """
1558        return _apply_conjunction_builder(
1559            *expressions,
1560            instance=self,
1561            arg="where",
1562            append=append,
1563            into=Where,
1564            dialect=dialect,
1565            copy=copy,
1566            **opts,
1567        )
1568
1569
1570class Drop(Expression):
1571    arg_types = {
1572        "this": False,
1573        "kind": False,
1574        "exists": False,
1575        "temporary": False,
1576        "materialized": False,
1577        "cascade": False,
1578        "constraints": False,
1579        "purge": False,
1580    }
1581
1582
1583class Filter(Expression):
1584    arg_types = {"this": True, "expression": True}
1585
1586
1587class Check(Expression):
1588    pass
1589
1590
1591# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1592class Connect(Expression):
1593    arg_types = {"start": False, "connect": True}
1594
1595
1596class Prior(Expression):
1597    pass
1598
1599
1600class Directory(Expression):
1601    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1602    arg_types = {"this": True, "local": False, "row_format": False}
1603
1604
1605class ForeignKey(Expression):
1606    arg_types = {
1607        "expressions": True,
1608        "reference": False,
1609        "delete": False,
1610        "update": False,
1611    }
1612
1613
1614class ColumnPrefix(Expression):
1615    arg_types = {"this": True, "expression": True}
1616
1617
1618class PrimaryKey(Expression):
1619    arg_types = {"expressions": True, "options": False}
1620
1621
1622# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1623# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1624class Into(Expression):
1625    arg_types = {"this": True, "temporary": False, "unlogged": False}
1626
1627
1628class From(Expression):
1629    @property
1630    def name(self) -> str:
1631        return self.this.name
1632
1633    @property
1634    def alias_or_name(self) -> str:
1635        return self.this.alias_or_name
1636
1637
1638class Having(Expression):
1639    pass
1640
1641
1642class Hint(Expression):
1643    arg_types = {"expressions": True}
1644
1645
1646class JoinHint(Expression):
1647    arg_types = {"this": True, "expressions": True}
1648
1649
1650class Identifier(Expression):
1651    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1652
1653    @property
1654    def quoted(self) -> bool:
1655        return bool(self.args.get("quoted"))
1656
1657    @property
1658    def hashable_args(self) -> t.Any:
1659        return (self.this, self.quoted)
1660
1661    @property
1662    def output_name(self) -> str:
1663        return self.name
1664
1665
1666# https://www.postgresql.org/docs/current/indexes-opclass.html
1667class Opclass(Expression):
1668    arg_types = {"this": True, "expression": True}
1669
1670
1671class Index(Expression):
1672    arg_types = {
1673        "this": False,
1674        "table": False,
1675        "using": False,
1676        "where": False,
1677        "columns": False,
1678        "unique": False,
1679        "primary": False,
1680        "amp": False,  # teradata
1681        "include": False,
1682        "partition_by": False,  # teradata
1683    }
1684
1685
1686class Insert(DDL, DML):
1687    arg_types = {
1688        "with": False,
1689        "this": True,
1690        "expression": False,
1691        "conflict": False,
1692        "returning": False,
1693        "overwrite": False,
1694        "exists": False,
1695        "partition": False,
1696        "alternative": False,
1697        "where": False,
1698        "ignore": False,
1699        "by_name": False,
1700    }
1701
1702    def with_(
1703        self,
1704        alias: ExpOrStr,
1705        as_: ExpOrStr,
1706        recursive: t.Optional[bool] = None,
1707        append: bool = True,
1708        dialect: DialectType = None,
1709        copy: bool = True,
1710        **opts,
1711    ) -> Insert:
1712        """
1713        Append to or set the common table expressions.
1714
1715        Example:
1716            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1717            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1718
1719        Args:
1720            alias: the SQL code string to parse as the table name.
1721                If an `Expression` instance is passed, this is used as-is.
1722            as_: the SQL code string to parse as the table expression.
1723                If an `Expression` instance is passed, it will be used as-is.
1724            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1725            append: if `True`, add to any existing expressions.
1726                Otherwise, this resets the expressions.
1727            dialect: the dialect used to parse the input expression.
1728            copy: if `False`, modify this expression instance in-place.
1729            opts: other options to use to parse the input expressions.
1730
1731        Returns:
1732            The modified expression.
1733        """
1734        return _apply_cte_builder(
1735            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1736        )
1737
1738
1739class OnConflict(Expression):
1740    arg_types = {
1741        "duplicate": False,
1742        "expressions": False,
1743        "nothing": False,
1744        "key": False,
1745        "constraint": False,
1746    }
1747
1748
1749class Returning(Expression):
1750    arg_types = {"expressions": True, "into": False}
1751
1752
1753# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1754class Introducer(Expression):
1755    arg_types = {"this": True, "expression": True}
1756
1757
1758# national char, like n'utf8'
1759class National(Expression):
1760    pass
1761
1762
1763class LoadData(Expression):
1764    arg_types = {
1765        "this": True,
1766        "local": False,
1767        "overwrite": False,
1768        "inpath": True,
1769        "partition": False,
1770        "input_format": False,
1771        "serde": False,
1772    }
1773
1774
1775class Partition(Expression):
1776    arg_types = {"expressions": True}
1777
1778
1779class Fetch(Expression):
1780    arg_types = {
1781        "direction": False,
1782        "count": False,
1783        "percent": False,
1784        "with_ties": False,
1785    }
1786
1787
1788class Group(Expression):
1789    arg_types = {
1790        "expressions": False,
1791        "grouping_sets": False,
1792        "cube": False,
1793        "rollup": False,
1794        "totals": False,
1795        "all": False,
1796    }
1797
1798
1799class Lambda(Expression):
1800    arg_types = {"this": True, "expressions": True}
1801
1802
1803class Limit(Expression):
1804    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
1805
1806
1807class Literal(Condition):
1808    arg_types = {"this": True, "is_string": True}
1809
1810    @property
1811    def hashable_args(self) -> t.Any:
1812        return (self.this, self.args.get("is_string"))
1813
1814    @classmethod
1815    def number(cls, number) -> Literal:
1816        return cls(this=str(number), is_string=False)
1817
1818    @classmethod
1819    def string(cls, string) -> Literal:
1820        return cls(this=str(string), is_string=True)
1821
1822    @property
1823    def output_name(self) -> str:
1824        return self.name
1825
1826
1827class Join(Expression):
1828    arg_types = {
1829        "this": True,
1830        "on": False,
1831        "side": False,
1832        "kind": False,
1833        "using": False,
1834        "method": False,
1835        "global": False,
1836        "hint": False,
1837    }
1838
1839    @property
1840    def method(self) -> str:
1841        return self.text("method").upper()
1842
1843    @property
1844    def kind(self) -> str:
1845        return self.text("kind").upper()
1846
1847    @property
1848    def side(self) -> str:
1849        return self.text("side").upper()
1850
1851    @property
1852    def hint(self) -> str:
1853        return self.text("hint").upper()
1854
1855    @property
1856    def alias_or_name(self) -> str:
1857        return self.this.alias_or_name
1858
1859    def on(
1860        self,
1861        *expressions: t.Optional[ExpOrStr],
1862        append: bool = True,
1863        dialect: DialectType = None,
1864        copy: bool = True,
1865        **opts,
1866    ) -> Join:
1867        """
1868        Append to or set the ON expressions.
1869
1870        Example:
1871            >>> import sqlglot
1872            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1873            'JOIN x ON y = 1'
1874
1875        Args:
1876            *expressions: the SQL code strings to parse.
1877                If an `Expression` instance is passed, it will be used as-is.
1878                Multiple expressions are combined with an AND operator.
1879            append: if `True`, AND the new expressions to any existing expression.
1880                Otherwise, this resets the expression.
1881            dialect: the dialect used to parse the input expressions.
1882            copy: if `False`, modify this expression instance in-place.
1883            opts: other options to use to parse the input expressions.
1884
1885        Returns:
1886            The modified Join expression.
1887        """
1888        join = _apply_conjunction_builder(
1889            *expressions,
1890            instance=self,
1891            arg="on",
1892            append=append,
1893            dialect=dialect,
1894            copy=copy,
1895            **opts,
1896        )
1897
1898        if join.kind == "CROSS":
1899            join.set("kind", None)
1900
1901        return join
1902
1903    def using(
1904        self,
1905        *expressions: t.Optional[ExpOrStr],
1906        append: bool = True,
1907        dialect: DialectType = None,
1908        copy: bool = True,
1909        **opts,
1910    ) -> Join:
1911        """
1912        Append to or set the USING expressions.
1913
1914        Example:
1915            >>> import sqlglot
1916            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1917            'JOIN x USING (foo, bla)'
1918
1919        Args:
1920            *expressions: the SQL code strings to parse.
1921                If an `Expression` instance is passed, it will be used as-is.
1922            append: if `True`, concatenate the new expressions to the existing "using" list.
1923                Otherwise, this resets the expression.
1924            dialect: the dialect used to parse the input expressions.
1925            copy: if `False`, modify this expression instance in-place.
1926            opts: other options to use to parse the input expressions.
1927
1928        Returns:
1929            The modified Join expression.
1930        """
1931        join = _apply_list_builder(
1932            *expressions,
1933            instance=self,
1934            arg="using",
1935            append=append,
1936            dialect=dialect,
1937            copy=copy,
1938            **opts,
1939        )
1940
1941        if join.kind == "CROSS":
1942            join.set("kind", None)
1943
1944        return join
1945
1946
1947class Lateral(UDTF):
1948    arg_types = {
1949        "this": True,
1950        "view": False,
1951        "outer": False,
1952        "alias": False,
1953        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
1954    }
1955
1956
1957class MatchRecognize(Expression):
1958    arg_types = {
1959        "partition_by": False,
1960        "order": False,
1961        "measures": False,
1962        "rows": False,
1963        "after": False,
1964        "pattern": False,
1965        "define": False,
1966        "alias": False,
1967    }
1968
1969
1970# Clickhouse FROM FINAL modifier
1971# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1972class Final(Expression):
1973    pass
1974
1975
1976class Offset(Expression):
1977    arg_types = {"this": False, "expression": True, "expressions": False}
1978
1979
1980class Order(Expression):
1981    arg_types = {
1982        "this": False,
1983        "expressions": True,
1984        "interpolate": False,
1985        "siblings": False,
1986    }
1987
1988
1989# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
1990class WithFill(Expression):
1991    arg_types = {"from": False, "to": False, "step": False}
1992
1993
1994# hive specific sorts
1995# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1996class Cluster(Order):
1997    pass
1998
1999
2000class Distribute(Order):
2001    pass
2002
2003
2004class Sort(Order):
2005    pass
2006
2007
2008class Ordered(Expression):
2009    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2010
2011
2012class Property(Expression):
2013    arg_types = {"this": True, "value": True}
2014
2015
2016class AlgorithmProperty(Property):
2017    arg_types = {"this": True}
2018
2019
2020class AutoIncrementProperty(Property):
2021    arg_types = {"this": True}
2022
2023
2024# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2025class AutoRefreshProperty(Property):
2026    arg_types = {"this": True}
2027
2028
2029class BlockCompressionProperty(Property):
2030    arg_types = {
2031        "autotemp": False,
2032        "always": False,
2033        "default": False,
2034        "manual": False,
2035        "never": False,
2036    }
2037
2038
2039class CharacterSetProperty(Property):
2040    arg_types = {"this": True, "default": True}
2041
2042
2043class ChecksumProperty(Property):
2044    arg_types = {"on": False, "default": False}
2045
2046
2047class CollateProperty(Property):
2048    arg_types = {"this": True, "default": False}
2049
2050
2051class CopyGrantsProperty(Property):
2052    arg_types = {}
2053
2054
2055class DataBlocksizeProperty(Property):
2056    arg_types = {
2057        "size": False,
2058        "units": False,
2059        "minimum": False,
2060        "maximum": False,
2061        "default": False,
2062    }
2063
2064
2065class DefinerProperty(Property):
2066    arg_types = {"this": True}
2067
2068
2069class DistKeyProperty(Property):
2070    arg_types = {"this": True}
2071
2072
2073class DistStyleProperty(Property):
2074    arg_types = {"this": True}
2075
2076
2077class EngineProperty(Property):
2078    arg_types = {"this": True}
2079
2080
2081class HeapProperty(Property):
2082    arg_types = {}
2083
2084
2085class ToTableProperty(Property):
2086    arg_types = {"this": True}
2087
2088
2089class ExecuteAsProperty(Property):
2090    arg_types = {"this": True}
2091
2092
2093class ExternalProperty(Property):
2094    arg_types = {"this": False}
2095
2096
2097class FallbackProperty(Property):
2098    arg_types = {"no": True, "protection": False}
2099
2100
2101class FileFormatProperty(Property):
2102    arg_types = {"this": True}
2103
2104
2105class FreespaceProperty(Property):
2106    arg_types = {"this": True, "percent": False}
2107
2108
2109class InheritsProperty(Property):
2110    arg_types = {"expressions": True}
2111
2112
2113class InputModelProperty(Property):
2114    arg_types = {"this": True}
2115
2116
2117class OutputModelProperty(Property):
2118    arg_types = {"this": True}
2119
2120
2121class IsolatedLoadingProperty(Property):
2122    arg_types = {
2123        "no": False,
2124        "concurrent": False,
2125        "for_all": False,
2126        "for_insert": False,
2127        "for_none": False,
2128    }
2129
2130
2131class JournalProperty(Property):
2132    arg_types = {
2133        "no": False,
2134        "dual": False,
2135        "before": False,
2136        "local": False,
2137        "after": False,
2138    }
2139
2140
2141class LanguageProperty(Property):
2142    arg_types = {"this": True}
2143
2144
2145# spark ddl
2146class ClusteredByProperty(Property):
2147    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2148
2149
2150class DictProperty(Property):
2151    arg_types = {"this": True, "kind": True, "settings": False}
2152
2153
2154class DictSubProperty(Property):
2155    pass
2156
2157
2158class DictRange(Property):
2159    arg_types = {"this": True, "min": True, "max": True}
2160
2161
2162# Clickhouse CREATE ... ON CLUSTER modifier
2163# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2164class OnCluster(Property):
2165    arg_types = {"this": True}
2166
2167
2168class LikeProperty(Property):
2169    arg_types = {"this": True, "expressions": False}
2170
2171
2172class LocationProperty(Property):
2173    arg_types = {"this": True}
2174
2175
2176class LockingProperty(Property):
2177    arg_types = {
2178        "this": False,
2179        "kind": True,
2180        "for_or_in": False,
2181        "lock_type": True,
2182        "override": False,
2183    }
2184
2185
2186class LogProperty(Property):
2187    arg_types = {"no": True}
2188
2189
2190class MaterializedProperty(Property):
2191    arg_types = {"this": False}
2192
2193
2194class MergeBlockRatioProperty(Property):
2195    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2196
2197
2198class NoPrimaryIndexProperty(Property):
2199    arg_types = {}
2200
2201
2202class OnProperty(Property):
2203    arg_types = {"this": True}
2204
2205
2206class OnCommitProperty(Property):
2207    arg_types = {"delete": False}
2208
2209
2210class PartitionedByProperty(Property):
2211    arg_types = {"this": True}
2212
2213
2214# https://www.postgresql.org/docs/current/sql-createtable.html
2215class PartitionBoundSpec(Expression):
2216    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2217    arg_types = {
2218        "this": False,
2219        "expression": False,
2220        "from_expressions": False,
2221        "to_expressions": False,
2222    }
2223
2224
2225class PartitionedOfProperty(Property):
2226    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2227    arg_types = {"this": True, "expression": True}
2228
2229
2230class RemoteWithConnectionModelProperty(Property):
2231    arg_types = {"this": True}
2232
2233
2234class ReturnsProperty(Property):
2235    arg_types = {"this": True, "is_table": False, "table": False}
2236
2237
2238class RowFormatProperty(Property):
2239    arg_types = {"this": True}
2240
2241
2242class RowFormatDelimitedProperty(Property):
2243    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2244    arg_types = {
2245        "fields": False,
2246        "escaped": False,
2247        "collection_items": False,
2248        "map_keys": False,
2249        "lines": False,
2250        "null": False,
2251        "serde": False,
2252    }
2253
2254
2255class RowFormatSerdeProperty(Property):
2256    arg_types = {"this": True, "serde_properties": False}
2257
2258
2259# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2260class QueryTransform(Expression):
2261    arg_types = {
2262        "expressions": True,
2263        "command_script": True,
2264        "schema": False,
2265        "row_format_before": False,
2266        "record_writer": False,
2267        "row_format_after": False,
2268        "record_reader": False,
2269    }
2270
2271
2272class SampleProperty(Property):
2273    arg_types = {"this": True}
2274
2275
2276class SchemaCommentProperty(Property):
2277    arg_types = {"this": True}
2278
2279
2280class SerdeProperties(Property):
2281    arg_types = {"expressions": True}
2282
2283
2284class SetProperty(Property):
2285    arg_types = {"multi": True}
2286
2287
2288class SetConfigProperty(Property):
2289    arg_types = {"this": True}
2290
2291
2292class SettingsProperty(Property):
2293    arg_types = {"expressions": True}
2294
2295
2296class SortKeyProperty(Property):
2297    arg_types = {"this": True, "compound": False}
2298
2299
2300class SqlReadWriteProperty(Property):
2301    arg_types = {"this": True}
2302
2303
2304class SqlSecurityProperty(Property):
2305    arg_types = {"definer": True}
2306
2307
2308class StabilityProperty(Property):
2309    arg_types = {"this": True}
2310
2311
2312class TemporaryProperty(Property):
2313    arg_types = {}
2314
2315
2316class TransformModelProperty(Property):
2317    arg_types = {"expressions": True}
2318
2319
2320class TransientProperty(Property):
2321    arg_types = {"this": False}
2322
2323
2324class VolatileProperty(Property):
2325    arg_types = {"this": False}
2326
2327
2328class WithDataProperty(Property):
2329    arg_types = {"no": True, "statistics": False}
2330
2331
2332class WithJournalTableProperty(Property):
2333    arg_types = {"this": True}
2334
2335
2336class WithSystemVersioningProperty(Property):
2337    # this -> history table name, expression -> data consistency check
2338    arg_types = {"this": False, "expression": False}
2339
2340
2341class Properties(Expression):
2342    arg_types = {"expressions": True}
2343
2344    NAME_TO_PROPERTY = {
2345        "ALGORITHM": AlgorithmProperty,
2346        "AUTO_INCREMENT": AutoIncrementProperty,
2347        "CHARACTER SET": CharacterSetProperty,
2348        "CLUSTERED_BY": ClusteredByProperty,
2349        "COLLATE": CollateProperty,
2350        "COMMENT": SchemaCommentProperty,
2351        "DEFINER": DefinerProperty,
2352        "DISTKEY": DistKeyProperty,
2353        "DISTSTYLE": DistStyleProperty,
2354        "ENGINE": EngineProperty,
2355        "EXECUTE AS": ExecuteAsProperty,
2356        "FORMAT": FileFormatProperty,
2357        "LANGUAGE": LanguageProperty,
2358        "LOCATION": LocationProperty,
2359        "PARTITIONED_BY": PartitionedByProperty,
2360        "RETURNS": ReturnsProperty,
2361        "ROW_FORMAT": RowFormatProperty,
2362        "SORTKEY": SortKeyProperty,
2363    }
2364
2365    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2366
2367    # CREATE property locations
2368    # Form: schema specified
2369    #   create [POST_CREATE]
2370    #     table a [POST_NAME]
2371    #     (b int) [POST_SCHEMA]
2372    #     with ([POST_WITH])
2373    #     index (b) [POST_INDEX]
2374    #
2375    # Form: alias selection
2376    #   create [POST_CREATE]
2377    #     table a [POST_NAME]
2378    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2379    #     index (c) [POST_INDEX]
2380    class Location(AutoName):
2381        POST_CREATE = auto()
2382        POST_NAME = auto()
2383        POST_SCHEMA = auto()
2384        POST_WITH = auto()
2385        POST_ALIAS = auto()
2386        POST_EXPRESSION = auto()
2387        POST_INDEX = auto()
2388        UNSUPPORTED = auto()
2389
2390    @classmethod
2391    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2392        expressions = []
2393        for key, value in properties_dict.items():
2394            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2395            if property_cls:
2396                expressions.append(property_cls(this=convert(value)))
2397            else:
2398                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2399
2400        return cls(expressions=expressions)
2401
2402
2403class Qualify(Expression):
2404    pass
2405
2406
2407class InputOutputFormat(Expression):
2408    arg_types = {"input_format": False, "output_format": False}
2409
2410
2411# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2412class Return(Expression):
2413    pass
2414
2415
2416class Reference(Expression):
2417    arg_types = {"this": True, "expressions": False, "options": False}
2418
2419
2420class Tuple(Expression):
2421    arg_types = {"expressions": False}
2422
2423    def isin(
2424        self,
2425        *expressions: t.Any,
2426        query: t.Optional[ExpOrStr] = None,
2427        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2428        copy: bool = True,
2429        **opts,
2430    ) -> In:
2431        return In(
2432            this=maybe_copy(self, copy),
2433            expressions=[convert(e, copy=copy) for e in expressions],
2434            query=maybe_parse(query, copy=copy, **opts) if query else None,
2435            unnest=(
2436                Unnest(
2437                    expressions=[
2438                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2439                        for e in ensure_list(unnest)
2440                    ]
2441                )
2442                if unnest
2443                else None
2444            ),
2445        )
2446
2447
2448class Subqueryable(Unionable):
2449    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2450        """
2451        Convert this expression to an aliased expression that can be used as a Subquery.
2452
2453        Example:
2454            >>> subquery = Select().select("x").from_("tbl").subquery()
2455            >>> Select().select("x").from_(subquery).sql()
2456            'SELECT x FROM (SELECT x FROM tbl)'
2457
2458        Args:
2459            alias (str | Identifier): an optional alias for the subquery
2460            copy (bool): if `False`, modify this expression instance in-place.
2461
2462        Returns:
2463            Alias: the subquery
2464        """
2465        instance = maybe_copy(self, copy)
2466        if not isinstance(alias, Expression):
2467            alias = TableAlias(this=to_identifier(alias)) if alias else None
2468
2469        return Subquery(this=instance, alias=alias)
2470
2471    def limit(
2472        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2473    ) -> Select:
2474        raise NotImplementedError
2475
2476    @property
2477    def ctes(self):
2478        with_ = self.args.get("with")
2479        if not with_:
2480            return []
2481        return with_.expressions
2482
2483    @property
2484    def selects(self) -> t.List[Expression]:
2485        raise NotImplementedError("Subqueryable objects must implement `selects`")
2486
2487    @property
2488    def named_selects(self) -> t.List[str]:
2489        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2490
2491    def select(
2492        self,
2493        *expressions: t.Optional[ExpOrStr],
2494        append: bool = True,
2495        dialect: DialectType = None,
2496        copy: bool = True,
2497        **opts,
2498    ) -> Subqueryable:
2499        raise NotImplementedError("Subqueryable objects must implement `select`")
2500
2501    def with_(
2502        self,
2503        alias: ExpOrStr,
2504        as_: ExpOrStr,
2505        recursive: t.Optional[bool] = None,
2506        append: bool = True,
2507        dialect: DialectType = None,
2508        copy: bool = True,
2509        **opts,
2510    ) -> Subqueryable:
2511        """
2512        Append to or set the common table expressions.
2513
2514        Example:
2515            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2516            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2517
2518        Args:
2519            alias: the SQL code string to parse as the table name.
2520                If an `Expression` instance is passed, this is used as-is.
2521            as_: the SQL code string to parse as the table expression.
2522                If an `Expression` instance is passed, it will be used as-is.
2523            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2524            append: if `True`, add to any existing expressions.
2525                Otherwise, this resets the expressions.
2526            dialect: the dialect used to parse the input expression.
2527            copy: if `False`, modify this expression instance in-place.
2528            opts: other options to use to parse the input expressions.
2529
2530        Returns:
2531            The modified expression.
2532        """
2533        return _apply_cte_builder(
2534            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2535        )
2536
2537
2538QUERY_MODIFIERS = {
2539    "match": False,
2540    "laterals": False,
2541    "joins": False,
2542    "connect": False,
2543    "pivots": False,
2544    "where": False,
2545    "group": False,
2546    "having": False,
2547    "qualify": False,
2548    "windows": False,
2549    "distribute": False,
2550    "sort": False,
2551    "cluster": False,
2552    "order": False,
2553    "limit": False,
2554    "offset": False,
2555    "locks": False,
2556    "sample": False,
2557    "settings": False,
2558    "format": False,
2559}
2560
2561
2562# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2563class WithTableHint(Expression):
2564    arg_types = {"expressions": True}
2565
2566
2567# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2568class IndexTableHint(Expression):
2569    arg_types = {"this": True, "expressions": False, "target": False}
2570
2571
2572# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2573class HistoricalData(Expression):
2574    arg_types = {"this": True, "kind": True, "expression": True}
2575
2576
2577class Table(Expression):
2578    arg_types = {
2579        "this": False,
2580        "alias": False,
2581        "db": False,
2582        "catalog": False,
2583        "laterals": False,
2584        "joins": False,
2585        "pivots": False,
2586        "hints": False,
2587        "system_time": False,
2588        "version": False,
2589        "format": False,
2590        "pattern": False,
2591        "ordinality": False,
2592        "when": False,
2593    }
2594
2595    @property
2596    def name(self) -> str:
2597        if isinstance(self.this, Func):
2598            return ""
2599        return self.this.name
2600
2601    @property
2602    def db(self) -> str:
2603        return self.text("db")
2604
2605    @property
2606    def catalog(self) -> str:
2607        return self.text("catalog")
2608
2609    @property
2610    def selects(self) -> t.List[Expression]:
2611        return []
2612
2613    @property
2614    def named_selects(self) -> t.List[str]:
2615        return []
2616
2617    @property
2618    def parts(self) -> t.List[Expression]:
2619        """Return the parts of a table in order catalog, db, table."""
2620        parts: t.List[Expression] = []
2621
2622        for arg in ("catalog", "db", "this"):
2623            part = self.args.get(arg)
2624
2625            if isinstance(part, Dot):
2626                parts.extend(part.flatten())
2627            elif isinstance(part, Expression):
2628                parts.append(part)
2629
2630        return parts
2631
2632    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2633        parts = self.parts
2634        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
2635        alias = self.args.get("alias")
2636        if alias:
2637            col = alias_(col, alias.this, copy=copy)
2638        return col
2639
2640
2641class Union(Subqueryable):
2642    arg_types = {
2643        "with": False,
2644        "this": True,
2645        "expression": True,
2646        "distinct": False,
2647        "by_name": False,
2648        **QUERY_MODIFIERS,
2649    }
2650
2651    def limit(
2652        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2653    ) -> Select:
2654        """
2655        Set the LIMIT expression.
2656
2657        Example:
2658            >>> select("1").union(select("1")).limit(1).sql()
2659            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2660
2661        Args:
2662            expression: the SQL code string to parse.
2663                This can also be an integer.
2664                If a `Limit` instance is passed, this is used as-is.
2665                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2666            dialect: the dialect used to parse the input expression.
2667            copy: if `False`, modify this expression instance in-place.
2668            opts: other options to use to parse the input expressions.
2669
2670        Returns:
2671            The limited subqueryable.
2672        """
2673        return (
2674            select("*")
2675            .from_(self.subquery(alias="_l_0", copy=copy))
2676            .limit(expression, dialect=dialect, copy=False, **opts)
2677        )
2678
2679    def select(
2680        self,
2681        *expressions: t.Optional[ExpOrStr],
2682        append: bool = True,
2683        dialect: DialectType = None,
2684        copy: bool = True,
2685        **opts,
2686    ) -> Union:
2687        """Append to or set the SELECT of the union recursively.
2688
2689        Example:
2690            >>> from sqlglot import parse_one
2691            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2692            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2693
2694        Args:
2695            *expressions: the SQL code strings to parse.
2696                If an `Expression` instance is passed, it will be used as-is.
2697            append: if `True`, add to any existing expressions.
2698                Otherwise, this resets the expressions.
2699            dialect: the dialect used to parse the input expressions.
2700            copy: if `False`, modify this expression instance in-place.
2701            opts: other options to use to parse the input expressions.
2702
2703        Returns:
2704            Union: the modified expression.
2705        """
2706        this = self.copy() if copy else self
2707        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2708        this.expression.unnest().select(
2709            *expressions, append=append, dialect=dialect, copy=False, **opts
2710        )
2711        return this
2712
2713    @property
2714    def named_selects(self) -> t.List[str]:
2715        return self.this.unnest().named_selects
2716
2717    @property
2718    def is_star(self) -> bool:
2719        return self.this.is_star or self.expression.is_star
2720
2721    @property
2722    def selects(self) -> t.List[Expression]:
2723        return self.this.unnest().selects
2724
2725    @property
2726    def left(self) -> Expression:
2727        return self.this
2728
2729    @property
2730    def right(self) -> Expression:
2731        return self.expression
2732
2733
2734class Except(Union):
2735    pass
2736
2737
2738class Intersect(Union):
2739    pass
2740
2741
2742class Unnest(UDTF):
2743    arg_types = {
2744        "expressions": True,
2745        "alias": False,
2746        "offset": False,
2747    }
2748
2749    @property
2750    def selects(self) -> t.List[Expression]:
2751        columns = super().selects
2752        offset = self.args.get("offset")
2753        if offset:
2754            columns = columns + [to_identifier("offset") if offset is True else offset]
2755        return columns
2756
2757
2758class Update(Expression):
2759    arg_types = {
2760        "with": False,
2761        "this": False,
2762        "expressions": True,
2763        "from": False,
2764        "where": False,
2765        "returning": False,
2766        "order": False,
2767        "limit": False,
2768    }
2769
2770
2771class Values(UDTF):
2772    arg_types = {"expressions": True, "alias": False}
2773
2774
2775class Var(Expression):
2776    pass
2777
2778
2779class Version(Expression):
2780    """
2781    Time travel, iceberg, bigquery etc
2782    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2783    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2784    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2785    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2786    this is either TIMESTAMP or VERSION
2787    kind is ("AS OF", "BETWEEN")
2788    """
2789
2790    arg_types = {"this": True, "kind": True, "expression": False}
2791
2792
2793class Schema(Expression):
2794    arg_types = {"this": False, "expressions": False}
2795
2796
2797# https://dev.mysql.com/doc/refman/8.0/en/select.html
2798# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2799class Lock(Expression):
2800    arg_types = {"update": True, "expressions": False, "wait": False}
2801
2802
2803class Select(Subqueryable):
2804    arg_types = {
2805        "with": False,
2806        "kind": False,
2807        "expressions": False,
2808        "hint": False,
2809        "distinct": False,
2810        "into": False,
2811        "from": False,
2812        **QUERY_MODIFIERS,
2813    }
2814
2815    def from_(
2816        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2817    ) -> Select:
2818        """
2819        Set the FROM expression.
2820
2821        Example:
2822            >>> Select().from_("tbl").select("x").sql()
2823            'SELECT x FROM tbl'
2824
2825        Args:
2826            expression : the SQL code strings to parse.
2827                If a `From` instance is passed, this is used as-is.
2828                If another `Expression` instance is passed, it will be wrapped in a `From`.
2829            dialect: the dialect used to parse the input expression.
2830            copy: if `False`, modify this expression instance in-place.
2831            opts: other options to use to parse the input expressions.
2832
2833        Returns:
2834            The modified Select expression.
2835        """
2836        return _apply_builder(
2837            expression=expression,
2838            instance=self,
2839            arg="from",
2840            into=From,
2841            prefix="FROM",
2842            dialect=dialect,
2843            copy=copy,
2844            **opts,
2845        )
2846
2847    def group_by(
2848        self,
2849        *expressions: t.Optional[ExpOrStr],
2850        append: bool = True,
2851        dialect: DialectType = None,
2852        copy: bool = True,
2853        **opts,
2854    ) -> Select:
2855        """
2856        Set the GROUP BY expression.
2857
2858        Example:
2859            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2860            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2861
2862        Args:
2863            *expressions: the SQL code strings to parse.
2864                If a `Group` instance is passed, this is used as-is.
2865                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2866                If nothing is passed in then a group by is not applied to the expression
2867            append: if `True`, add to any existing expressions.
2868                Otherwise, this flattens all the `Group` expression into a single expression.
2869            dialect: the dialect used to parse the input expression.
2870            copy: if `False`, modify this expression instance in-place.
2871            opts: other options to use to parse the input expressions.
2872
2873        Returns:
2874            The modified Select expression.
2875        """
2876        if not expressions:
2877            return self if not copy else self.copy()
2878
2879        return _apply_child_list_builder(
2880            *expressions,
2881            instance=self,
2882            arg="group",
2883            append=append,
2884            copy=copy,
2885            prefix="GROUP BY",
2886            into=Group,
2887            dialect=dialect,
2888            **opts,
2889        )
2890
2891    def order_by(
2892        self,
2893        *expressions: t.Optional[ExpOrStr],
2894        append: bool = True,
2895        dialect: DialectType = None,
2896        copy: bool = True,
2897        **opts,
2898    ) -> Select:
2899        """
2900        Set the ORDER BY expression.
2901
2902        Example:
2903            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2904            'SELECT x FROM tbl ORDER BY x DESC'
2905
2906        Args:
2907            *expressions: the SQL code strings to parse.
2908                If a `Group` instance is passed, this is used as-is.
2909                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2910            append: if `True`, add to any existing expressions.
2911                Otherwise, this flattens all the `Order` expression into a single expression.
2912            dialect: the dialect used to parse the input expression.
2913            copy: if `False`, modify this expression instance in-place.
2914            opts: other options to use to parse the input expressions.
2915
2916        Returns:
2917            The modified Select expression.
2918        """
2919        return _apply_child_list_builder(
2920            *expressions,
2921            instance=self,
2922            arg="order",
2923            append=append,
2924            copy=copy,
2925            prefix="ORDER BY",
2926            into=Order,
2927            dialect=dialect,
2928            **opts,
2929        )
2930
2931    def sort_by(
2932        self,
2933        *expressions: t.Optional[ExpOrStr],
2934        append: bool = True,
2935        dialect: DialectType = None,
2936        copy: bool = True,
2937        **opts,
2938    ) -> Select:
2939        """
2940        Set the SORT BY expression.
2941
2942        Example:
2943            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2944            'SELECT x FROM tbl SORT BY x DESC'
2945
2946        Args:
2947            *expressions: the SQL code strings to parse.
2948                If a `Group` instance is passed, this is used as-is.
2949                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2950            append: if `True`, add to any existing expressions.
2951                Otherwise, this flattens all the `Order` expression into a single expression.
2952            dialect: the dialect used to parse the input expression.
2953            copy: if `False`, modify this expression instance in-place.
2954            opts: other options to use to parse the input expressions.
2955
2956        Returns:
2957            The modified Select expression.
2958        """
2959        return _apply_child_list_builder(
2960            *expressions,
2961            instance=self,
2962            arg="sort",
2963            append=append,
2964            copy=copy,
2965            prefix="SORT BY",
2966            into=Sort,
2967            dialect=dialect,
2968            **opts,
2969        )
2970
2971    def cluster_by(
2972        self,
2973        *expressions: t.Optional[ExpOrStr],
2974        append: bool = True,
2975        dialect: DialectType = None,
2976        copy: bool = True,
2977        **opts,
2978    ) -> Select:
2979        """
2980        Set the CLUSTER BY expression.
2981
2982        Example:
2983            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2984            'SELECT x FROM tbl CLUSTER BY x DESC'
2985
2986        Args:
2987            *expressions: the SQL code strings to parse.
2988                If a `Group` instance is passed, this is used as-is.
2989                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2990            append: if `True`, add to any existing expressions.
2991                Otherwise, this flattens all the `Order` expression into a single expression.
2992            dialect: the dialect used to parse the input expression.
2993            copy: if `False`, modify this expression instance in-place.
2994            opts: other options to use to parse the input expressions.
2995
2996        Returns:
2997            The modified Select expression.
2998        """
2999        return _apply_child_list_builder(
3000            *expressions,
3001            instance=self,
3002            arg="cluster",
3003            append=append,
3004            copy=copy,
3005            prefix="CLUSTER BY",
3006            into=Cluster,
3007            dialect=dialect,
3008            **opts,
3009        )
3010
3011    def limit(
3012        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3013    ) -> Select:
3014        """
3015        Set the LIMIT expression.
3016
3017        Example:
3018            >>> Select().from_("tbl").select("x").limit(10).sql()
3019            'SELECT x FROM tbl LIMIT 10'
3020
3021        Args:
3022            expression: the SQL code string to parse.
3023                This can also be an integer.
3024                If a `Limit` instance is passed, this is used as-is.
3025                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
3026            dialect: the dialect used to parse the input expression.
3027            copy: if `False`, modify this expression instance in-place.
3028            opts: other options to use to parse the input expressions.
3029
3030        Returns:
3031            Select: the modified expression.
3032        """
3033        return _apply_builder(
3034            expression=expression,
3035            instance=self,
3036            arg="limit",
3037            into=Limit,
3038            prefix="LIMIT",
3039            dialect=dialect,
3040            copy=copy,
3041            into_arg="expression",
3042            **opts,
3043        )
3044
3045    def offset(
3046        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
3047    ) -> Select:
3048        """
3049        Set the OFFSET expression.
3050
3051        Example:
3052            >>> Select().from_("tbl").select("x").offset(10).sql()
3053            'SELECT x FROM tbl OFFSET 10'
3054
3055        Args:
3056            expression: the SQL code string to parse.
3057                This can also be an integer.
3058                If a `Offset` instance is passed, this is used as-is.
3059                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3060            dialect: the dialect used to parse the input expression.
3061            copy: if `False`, modify this expression instance in-place.
3062            opts: other options to use to parse the input expressions.
3063
3064        Returns:
3065            The modified Select expression.
3066        """
3067        return _apply_builder(
3068            expression=expression,
3069            instance=self,
3070            arg="offset",
3071            into=Offset,
3072            prefix="OFFSET",
3073            dialect=dialect,
3074            copy=copy,
3075            into_arg="expression",
3076            **opts,
3077        )
3078
3079    def select(
3080        self,
3081        *expressions: t.Optional[ExpOrStr],
3082        append: bool = True,
3083        dialect: DialectType = None,
3084        copy: bool = True,
3085        **opts,
3086    ) -> Select:
3087        """
3088        Append to or set the SELECT expressions.
3089
3090        Example:
3091            >>> Select().select("x", "y").sql()
3092            'SELECT x, y'
3093
3094        Args:
3095            *expressions: the SQL code strings to parse.
3096                If an `Expression` instance is passed, it will be used as-is.
3097            append: if `True`, add to any existing expressions.
3098                Otherwise, this resets the expressions.
3099            dialect: the dialect used to parse the input expressions.
3100            copy: if `False`, modify this expression instance in-place.
3101            opts: other options to use to parse the input expressions.
3102
3103        Returns:
3104            The modified Select expression.
3105        """
3106        return _apply_list_builder(
3107            *expressions,
3108            instance=self,
3109            arg="expressions",
3110            append=append,
3111            dialect=dialect,
3112            copy=copy,
3113            **opts,
3114        )
3115
3116    def lateral(
3117        self,
3118        *expressions: t.Optional[ExpOrStr],
3119        append: bool = True,
3120        dialect: DialectType = None,
3121        copy: bool = True,
3122        **opts,
3123    ) -> Select:
3124        """
3125        Append to or set the LATERAL expressions.
3126
3127        Example:
3128            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3129            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3130
3131        Args:
3132            *expressions: the SQL code strings to parse.
3133                If an `Expression` instance is passed, it will be used as-is.
3134            append: if `True`, add to any existing expressions.
3135                Otherwise, this resets the expressions.
3136            dialect: the dialect used to parse the input expressions.
3137            copy: if `False`, modify this expression instance in-place.
3138            opts: other options to use to parse the input expressions.
3139
3140        Returns:
3141            The modified Select expression.
3142        """
3143        return _apply_list_builder(
3144            *expressions,
3145            instance=self,
3146            arg="laterals",
3147            append=append,
3148            into=Lateral,
3149            prefix="LATERAL VIEW",
3150            dialect=dialect,
3151            copy=copy,
3152            **opts,
3153        )
3154
3155    def join(
3156        self,
3157        expression: ExpOrStr,
3158        on: t.Optional[ExpOrStr] = None,
3159        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3160        append: bool = True,
3161        join_type: t.Optional[str] = None,
3162        join_alias: t.Optional[Identifier | str] = None,
3163        dialect: DialectType = None,
3164        copy: bool = True,
3165        **opts,
3166    ) -> Select:
3167        """
3168        Append to or set the JOIN expressions.
3169
3170        Example:
3171            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3172            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3173
3174            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3175            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3176
3177            Use `join_type` to change the type of join:
3178
3179            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3180            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3181
3182        Args:
3183            expression: the SQL code string to parse.
3184                If an `Expression` instance is passed, it will be used as-is.
3185            on: optionally specify the join "on" criteria as a SQL string.
3186                If an `Expression` instance is passed, it will be used as-is.
3187            using: optionally specify the join "using" criteria as a SQL string.
3188                If an `Expression` instance is passed, it will be used as-is.
3189            append: if `True`, add to any existing expressions.
3190                Otherwise, this resets the expressions.
3191            join_type: if set, alter the parsed join type.
3192            join_alias: an optional alias for the joined source.
3193            dialect: the dialect used to parse the input expressions.
3194            copy: if `False`, modify this expression instance in-place.
3195            opts: other options to use to parse the input expressions.
3196
3197        Returns:
3198            Select: the modified expression.
3199        """
3200        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3201
3202        try:
3203            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3204        except ParseError:
3205            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3206
3207        join = expression if isinstance(expression, Join) else Join(this=expression)
3208
3209        if isinstance(join.this, Select):
3210            join.this.replace(join.this.subquery())
3211
3212        if join_type:
3213            method: t.Optional[Token]
3214            side: t.Optional[Token]
3215            kind: t.Optional[Token]
3216
3217            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3218
3219            if method:
3220                join.set("method", method.text)
3221            if side:
3222                join.set("side", side.text)
3223            if kind:
3224                join.set("kind", kind.text)
3225
3226        if on:
3227            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3228            join.set("on", on)
3229
3230        if using:
3231            join = _apply_list_builder(
3232                *ensure_list(using),
3233                instance=join,
3234                arg="using",
3235                append=append,
3236                copy=copy,
3237                into=Identifier,
3238                **opts,
3239            )
3240
3241        if join_alias:
3242            join.set("this", alias_(join.this, join_alias, table=True))
3243
3244        return _apply_list_builder(
3245            join,
3246            instance=self,
3247            arg="joins",
3248            append=append,
3249            copy=copy,
3250            **opts,
3251        )
3252
3253    def where(
3254        self,
3255        *expressions: t.Optional[ExpOrStr],
3256        append: bool = True,
3257        dialect: DialectType = None,
3258        copy: bool = True,
3259        **opts,
3260    ) -> Select:
3261        """
3262        Append to or set the WHERE expressions.
3263
3264        Example:
3265            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3266            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3267
3268        Args:
3269            *expressions: the SQL code strings to parse.
3270                If an `Expression` instance is passed, it will be used as-is.
3271                Multiple expressions are combined with an AND operator.
3272            append: if `True`, AND the new expressions to any existing expression.
3273                Otherwise, this resets the expression.
3274            dialect: the dialect used to parse the input expressions.
3275            copy: if `False`, modify this expression instance in-place.
3276            opts: other options to use to parse the input expressions.
3277
3278        Returns:
3279            Select: the modified expression.
3280        """
3281        return _apply_conjunction_builder(
3282            *expressions,
3283            instance=self,
3284            arg="where",
3285            append=append,
3286            into=Where,
3287            dialect=dialect,
3288            copy=copy,
3289            **opts,
3290        )
3291
3292    def having(
3293        self,
3294        *expressions: t.Optional[ExpOrStr],
3295        append: bool = True,
3296        dialect: DialectType = None,
3297        copy: bool = True,
3298        **opts,
3299    ) -> Select:
3300        """
3301        Append to or set the HAVING expressions.
3302
3303        Example:
3304            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3305            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3306
3307        Args:
3308            *expressions: the SQL code strings to parse.
3309                If an `Expression` instance is passed, it will be used as-is.
3310                Multiple expressions are combined with an AND operator.
3311            append: if `True`, AND the new expressions to any existing expression.
3312                Otherwise, this resets the expression.
3313            dialect: the dialect used to parse the input expressions.
3314            copy: if `False`, modify this expression instance in-place.
3315            opts: other options to use to parse the input expressions.
3316
3317        Returns:
3318            The modified Select expression.
3319        """
3320        return _apply_conjunction_builder(
3321            *expressions,
3322            instance=self,
3323            arg="having",
3324            append=append,
3325            into=Having,
3326            dialect=dialect,
3327            copy=copy,
3328            **opts,
3329        )
3330
3331    def window(
3332        self,
3333        *expressions: t.Optional[ExpOrStr],
3334        append: bool = True,
3335        dialect: DialectType = None,
3336        copy: bool = True,
3337        **opts,
3338    ) -> Select:
3339        return _apply_list_builder(
3340            *expressions,
3341            instance=self,
3342            arg="windows",
3343            append=append,
3344            into=Window,
3345            dialect=dialect,
3346            copy=copy,
3347            **opts,
3348        )
3349
3350    def qualify(
3351        self,
3352        *expressions: t.Optional[ExpOrStr],
3353        append: bool = True,
3354        dialect: DialectType = None,
3355        copy: bool = True,
3356        **opts,
3357    ) -> Select:
3358        return _apply_conjunction_builder(
3359            *expressions,
3360            instance=self,
3361            arg="qualify",
3362            append=append,
3363            into=Qualify,
3364            dialect=dialect,
3365            copy=copy,
3366            **opts,
3367        )
3368
3369    def distinct(
3370        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3371    ) -> Select:
3372        """
3373        Set the OFFSET expression.
3374
3375        Example:
3376            >>> Select().from_("tbl").select("x").distinct().sql()
3377            'SELECT DISTINCT x FROM tbl'
3378
3379        Args:
3380            ons: the expressions to distinct on
3381            distinct: whether the Select should be distinct
3382            copy: if `False`, modify this expression instance in-place.
3383
3384        Returns:
3385            Select: the modified expression.
3386        """
3387        instance = maybe_copy(self, copy)
3388        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3389        instance.set("distinct", Distinct(on=on) if distinct else None)
3390        return instance
3391
3392    def ctas(
3393        self,
3394        table: ExpOrStr,
3395        properties: t.Optional[t.Dict] = None,
3396        dialect: DialectType = None,
3397        copy: bool = True,
3398        **opts,
3399    ) -> Create:
3400        """
3401        Convert this expression to a CREATE TABLE AS statement.
3402
3403        Example:
3404            >>> Select().select("*").from_("tbl").ctas("x").sql()
3405            'CREATE TABLE x AS SELECT * FROM tbl'
3406
3407        Args:
3408            table: the SQL code string to parse as the table name.
3409                If another `Expression` instance is passed, it will be used as-is.
3410            properties: an optional mapping of table properties
3411            dialect: the dialect used to parse the input table.
3412            copy: if `False`, modify this expression instance in-place.
3413            opts: other options to use to parse the input table.
3414
3415        Returns:
3416            The new Create expression.
3417        """
3418        instance = maybe_copy(self, copy)
3419        table_expression = maybe_parse(
3420            table,
3421            into=Table,
3422            dialect=dialect,
3423            **opts,
3424        )
3425        properties_expression = None
3426        if properties:
3427            properties_expression = Properties.from_dict(properties)
3428
3429        return Create(
3430            this=table_expression,
3431            kind="TABLE",
3432            expression=instance,
3433            properties=properties_expression,
3434        )
3435
3436    def lock(self, update: bool = True, copy: bool = True) -> Select:
3437        """
3438        Set the locking read mode for this expression.
3439
3440        Examples:
3441            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3442            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3443
3444            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3445            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3446
3447        Args:
3448            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3449            copy: if `False`, modify this expression instance in-place.
3450
3451        Returns:
3452            The modified expression.
3453        """
3454        inst = maybe_copy(self, copy)
3455        inst.set("locks", [Lock(update=update)])
3456
3457        return inst
3458
3459    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3460        """
3461        Set hints for this expression.
3462
3463        Examples:
3464            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3465            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3466
3467        Args:
3468            hints: The SQL code strings to parse as the hints.
3469                If an `Expression` instance is passed, it will be used as-is.
3470            dialect: The dialect used to parse the hints.
3471            copy: If `False`, modify this expression instance in-place.
3472
3473        Returns:
3474            The modified expression.
3475        """
3476        inst = maybe_copy(self, copy)
3477        inst.set(
3478            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3479        )
3480
3481        return inst
3482
3483    @property
3484    def named_selects(self) -> t.List[str]:
3485        return [e.output_name for e in self.expressions if e.alias_or_name]
3486
3487    @property
3488    def is_star(self) -> bool:
3489        return any(expression.is_star for expression in self.expressions)
3490
3491    @property
3492    def selects(self) -> t.List[Expression]:
3493        return self.expressions
3494
3495
3496class Subquery(DerivedTable, Unionable):
3497    arg_types = {
3498        "this": True,
3499        "alias": False,
3500        "with": False,
3501        **QUERY_MODIFIERS,
3502    }
3503
3504    def unnest(self):
3505        """
3506        Returns the first non subquery.
3507        """
3508        expression = self
3509        while isinstance(expression, Subquery):
3510            expression = expression.this
3511        return expression
3512
3513    def unwrap(self) -> Subquery:
3514        expression = self
3515        while expression.same_parent and expression.is_wrapper:
3516            expression = t.cast(Subquery, expression.parent)
3517        return expression
3518
3519    @property
3520    def is_wrapper(self) -> bool:
3521        """
3522        Whether this Subquery acts as a simple wrapper around another expression.
3523
3524        SELECT * FROM (((SELECT * FROM t)))
3525                      ^
3526                      This corresponds to a "wrapper" Subquery node
3527        """
3528        return all(v is None for k, v in self.args.items() if k != "this")
3529
3530    @property
3531    def is_star(self) -> bool:
3532        return self.this.is_star
3533
3534    @property
3535    def output_name(self) -> str:
3536        return self.alias
3537
3538
3539class TableSample(Expression):
3540    arg_types = {
3541        "this": False,
3542        "expressions": False,
3543        "method": False,
3544        "bucket_numerator": False,
3545        "bucket_denominator": False,
3546        "bucket_field": False,
3547        "percent": False,
3548        "rows": False,
3549        "size": False,
3550        "seed": False,
3551    }
3552
3553
3554class Tag(Expression):
3555    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3556
3557    arg_types = {
3558        "this": False,
3559        "prefix": False,
3560        "postfix": False,
3561    }
3562
3563
3564# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3565# https://duckdb.org/docs/sql/statements/pivot
3566class Pivot(Expression):
3567    arg_types = {
3568        "this": False,
3569        "alias": False,
3570        "expressions": False,
3571        "field": False,
3572        "unpivot": False,
3573        "using": False,
3574        "group": False,
3575        "columns": False,
3576        "include_nulls": False,
3577    }
3578
3579    @property
3580    def unpivot(self) -> bool:
3581        return bool(self.args.get("unpivot"))
3582
3583
3584class Window(Condition):
3585    arg_types = {
3586        "this": True,
3587        "partition_by": False,
3588        "order": False,
3589        "spec": False,
3590        "alias": False,
3591        "over": False,
3592        "first": False,
3593    }
3594
3595
3596class WindowSpec(Expression):
3597    arg_types = {
3598        "kind": False,
3599        "start": False,
3600        "start_side": False,
3601        "end": False,
3602        "end_side": False,
3603    }
3604
3605
3606class Where(Expression):
3607    pass
3608
3609
3610class Star(Expression):
3611    arg_types = {"except": False, "replace": False}
3612
3613    @property
3614    def name(self) -> str:
3615        return "*"
3616
3617    @property
3618    def output_name(self) -> str:
3619        return self.name
3620
3621
3622class Parameter(Condition):
3623    arg_types = {"this": True, "expression": False}
3624
3625
3626class SessionParameter(Condition):
3627    arg_types = {"this": True, "kind": False}
3628
3629
3630class Placeholder(Condition):
3631    arg_types = {"this": False, "kind": False}
3632
3633
3634class Null(Condition):
3635    arg_types: t.Dict[str, t.Any] = {}
3636
3637    @property
3638    def name(self) -> str:
3639        return "NULL"
3640
3641
3642class Boolean(Condition):
3643    pass
3644
3645
3646class DataTypeParam(Expression):
3647    arg_types = {"this": True, "expression": False}
3648
3649
3650class DataType(Expression):
3651    arg_types = {
3652        "this": True,
3653        "expressions": False,
3654        "nested": False,
3655        "values": False,
3656        "prefix": False,
3657        "kind": False,
3658    }
3659
3660    class Type(AutoName):
3661        ARRAY = auto()
3662        AGGREGATEFUNCTION = auto()
3663        SIMPLEAGGREGATEFUNCTION = auto()
3664        BIGDECIMAL = auto()
3665        BIGINT = auto()
3666        BIGSERIAL = auto()
3667        BINARY = auto()
3668        BIT = auto()
3669        BOOLEAN = auto()
3670        BPCHAR = auto()
3671        CHAR = auto()
3672        DATE = auto()
3673        DATE32 = auto()
3674        DATEMULTIRANGE = auto()
3675        DATERANGE = auto()
3676        DATETIME = auto()
3677        DATETIME64 = auto()
3678        DECIMAL = auto()
3679        DOUBLE = auto()
3680        ENUM = auto()
3681        ENUM8 = auto()
3682        ENUM16 = auto()
3683        FIXEDSTRING = auto()
3684        FLOAT = auto()
3685        GEOGRAPHY = auto()
3686        GEOMETRY = auto()
3687        HLLSKETCH = auto()
3688        HSTORE = auto()
3689        IMAGE = auto()
3690        INET = auto()
3691        INT = auto()
3692        INT128 = auto()
3693        INT256 = auto()
3694        INT4MULTIRANGE = auto()
3695        INT4RANGE = auto()
3696        INT8MULTIRANGE = auto()
3697        INT8RANGE = auto()
3698        INTERVAL = auto()
3699        IPADDRESS = auto()
3700        IPPREFIX = auto()
3701        IPV4 = auto()
3702        IPV6 = auto()
3703        JSON = auto()
3704        JSONB = auto()
3705        LONGBLOB = auto()
3706        LONGTEXT = auto()
3707        LOWCARDINALITY = auto()
3708        MAP = auto()
3709        MEDIUMBLOB = auto()
3710        MEDIUMINT = auto()
3711        MEDIUMTEXT = auto()
3712        MONEY = auto()
3713        NCHAR = auto()
3714        NESTED = auto()
3715        NULL = auto()
3716        NULLABLE = auto()
3717        NUMMULTIRANGE = auto()
3718        NUMRANGE = auto()
3719        NVARCHAR = auto()
3720        OBJECT = auto()
3721        ROWVERSION = auto()
3722        SERIAL = auto()
3723        SET = auto()
3724        SMALLINT = auto()
3725        SMALLMONEY = auto()
3726        SMALLSERIAL = auto()
3727        STRUCT = auto()
3728        SUPER = auto()
3729        TEXT = auto()
3730        TINYBLOB = auto()
3731        TINYTEXT = auto()
3732        TIME = auto()
3733        TIMETZ = auto()
3734        TIMESTAMP = auto()
3735        TIMESTAMPLTZ = auto()
3736        TIMESTAMPTZ = auto()
3737        TIMESTAMP_S = auto()
3738        TIMESTAMP_MS = auto()
3739        TIMESTAMP_NS = auto()
3740        TINYINT = auto()
3741        TSMULTIRANGE = auto()
3742        TSRANGE = auto()
3743        TSTZMULTIRANGE = auto()
3744        TSTZRANGE = auto()
3745        UBIGINT = auto()
3746        UINT = auto()
3747        UINT128 = auto()
3748        UINT256 = auto()
3749        UMEDIUMINT = auto()
3750        UDECIMAL = auto()
3751        UNIQUEIDENTIFIER = auto()
3752        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3753        USERDEFINED = "USER-DEFINED"
3754        USMALLINT = auto()
3755        UTINYINT = auto()
3756        UUID = auto()
3757        VARBINARY = auto()
3758        VARCHAR = auto()
3759        VARIANT = auto()
3760        XML = auto()
3761        YEAR = auto()
3762
3763    TEXT_TYPES = {
3764        Type.CHAR,
3765        Type.NCHAR,
3766        Type.VARCHAR,
3767        Type.NVARCHAR,
3768        Type.TEXT,
3769    }
3770
3771    INTEGER_TYPES = {
3772        Type.INT,
3773        Type.TINYINT,
3774        Type.SMALLINT,
3775        Type.BIGINT,
3776        Type.INT128,
3777        Type.INT256,
3778        Type.BIT,
3779    }
3780
3781    FLOAT_TYPES = {
3782        Type.FLOAT,
3783        Type.DOUBLE,
3784    }
3785
3786    NUMERIC_TYPES = {
3787        *INTEGER_TYPES,
3788        *FLOAT_TYPES,
3789    }
3790
3791    TEMPORAL_TYPES = {
3792        Type.TIME,
3793        Type.TIMETZ,
3794        Type.TIMESTAMP,
3795        Type.TIMESTAMPTZ,
3796        Type.TIMESTAMPLTZ,
3797        Type.TIMESTAMP_S,
3798        Type.TIMESTAMP_MS,
3799        Type.TIMESTAMP_NS,
3800        Type.DATE,
3801        Type.DATE32,
3802        Type.DATETIME,
3803        Type.DATETIME64,
3804    }
3805
3806    @classmethod
3807    def build(
3808        cls,
3809        dtype: DATA_TYPE,
3810        dialect: DialectType = None,
3811        udt: bool = False,
3812        copy: bool = True,
3813        **kwargs,
3814    ) -> DataType:
3815        """
3816        Constructs a DataType object.
3817
3818        Args:
3819            dtype: the data type of interest.
3820            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3821            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3822                DataType, thus creating a user-defined type.
3823            copy: whether to copy the data type.
3824            kwargs: additional arguments to pass in the constructor of DataType.
3825
3826        Returns:
3827            The constructed DataType object.
3828        """
3829        from sqlglot import parse_one
3830
3831        if isinstance(dtype, str):
3832            if dtype.upper() == "UNKNOWN":
3833                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3834
3835            try:
3836                data_type_exp = parse_one(
3837                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3838                )
3839            except ParseError:
3840                if udt:
3841                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3842                raise
3843        elif isinstance(dtype, DataType.Type):
3844            data_type_exp = DataType(this=dtype)
3845        elif isinstance(dtype, DataType):
3846            return maybe_copy(dtype, copy)
3847        else:
3848            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3849
3850        return DataType(**{**data_type_exp.args, **kwargs})
3851
3852    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3853        """
3854        Checks whether this DataType matches one of the provided data types. Nested types or precision
3855        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3856
3857        Args:
3858            dtypes: the data types to compare this DataType to.
3859
3860        Returns:
3861            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3862        """
3863        for dtype in dtypes:
3864            other = DataType.build(dtype, copy=False, udt=True)
3865
3866            if (
3867                other.expressions
3868                or self.this == DataType.Type.USERDEFINED
3869                or other.this == DataType.Type.USERDEFINED
3870            ):
3871                matches = self == other
3872            else:
3873                matches = self.this == other.this
3874
3875            if matches:
3876                return True
3877        return False
3878
3879
3880DATA_TYPE = t.Union[str, DataType, DataType.Type]
3881
3882
3883# https://www.postgresql.org/docs/15/datatype-pseudo.html
3884class PseudoType(DataType):
3885    arg_types = {"this": True}
3886
3887
3888# https://www.postgresql.org/docs/15/datatype-oid.html
3889class ObjectIdentifier(DataType):
3890    arg_types = {"this": True}
3891
3892
3893# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3894class SubqueryPredicate(Predicate):
3895    pass
3896
3897
3898class All(SubqueryPredicate):
3899    pass
3900
3901
3902class Any(SubqueryPredicate):
3903    pass
3904
3905
3906class Exists(SubqueryPredicate):
3907    pass
3908
3909
3910# Commands to interact with the databases or engines. For most of the command
3911# expressions we parse whatever comes after the command's name as a string.
3912class Command(Expression):
3913    arg_types = {"this": True, "expression": False}
3914
3915
3916class Transaction(Expression):
3917    arg_types = {"this": False, "modes": False, "mark": False}
3918
3919
3920class Commit(Expression):
3921    arg_types = {"chain": False, "this": False, "durability": False}
3922
3923
3924class Rollback(Expression):
3925    arg_types = {"savepoint": False, "this": False}
3926
3927
3928class AlterTable(Expression):
3929    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3930
3931
3932class AddConstraint(Expression):
3933    arg_types = {"expressions": True}
3934
3935
3936class DropPartition(Expression):
3937    arg_types = {"expressions": True, "exists": False}
3938
3939
3940# Binary expressions like (ADD a b)
3941class Binary(Condition):
3942    arg_types = {"this": True, "expression": True}
3943
3944    @property
3945    def left(self) -> Expression:
3946        return self.this
3947
3948    @property
3949    def right(self) -> Expression:
3950        return self.expression
3951
3952
3953class Add(Binary):
3954    pass
3955
3956
3957class Connector(Binary):
3958    pass
3959
3960
3961class And(Connector):
3962    pass
3963
3964
3965class Or(Connector):
3966    pass
3967
3968
3969class BitwiseAnd(Binary):
3970    pass
3971
3972
3973class BitwiseLeftShift(Binary):
3974    pass
3975
3976
3977class BitwiseOr(Binary):
3978    pass
3979
3980
3981class BitwiseRightShift(Binary):
3982    pass
3983
3984
3985class BitwiseXor(Binary):
3986    pass
3987
3988
3989class Div(Binary):
3990    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3991
3992
3993class Overlaps(Binary):
3994    pass
3995
3996
3997class Dot(Binary):
3998    @property
3999    def name(self) -> str:
4000        return self.expression.name
4001
4002    @property
4003    def output_name(self) -> str:
4004        return self.name
4005
4006    @classmethod
4007    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4008        """Build a Dot object with a sequence of expressions."""
4009        if len(expressions) < 2:
4010            raise ValueError("Dot requires >= 2 expressions.")
4011
4012        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4013
4014    @property
4015    def parts(self) -> t.List[Expression]:
4016        """Return the parts of a table / column in order catalog, db, table."""
4017        this, *parts = self.flatten()
4018
4019        parts.reverse()
4020
4021        for arg in ("this", "table", "db", "catalog"):
4022            part = this.args.get(arg)
4023
4024            if isinstance(part, Expression):
4025                parts.append(part)
4026
4027        parts.reverse()
4028        return parts
4029
4030
4031class DPipe(Binary):
4032    arg_types = {"this": True, "expression": True, "safe": False}
4033
4034
4035class EQ(Binary, Predicate):
4036    pass
4037
4038
4039class NullSafeEQ(Binary, Predicate):
4040    pass
4041
4042
4043class NullSafeNEQ(Binary, Predicate):
4044    pass
4045
4046
4047# Represents e.g. := in DuckDB which is mostly used for setting parameters
4048class PropertyEQ(Binary):
4049    pass
4050
4051
4052class Distance(Binary):
4053    pass
4054
4055
4056class Escape(Binary):
4057    pass
4058
4059
4060class Glob(Binary, Predicate):
4061    pass
4062
4063
4064class GT(Binary, Predicate):
4065    pass
4066
4067
4068class GTE(Binary, Predicate):
4069    pass
4070
4071
4072class ILike(Binary, Predicate):
4073    pass
4074
4075
4076class ILikeAny(Binary, Predicate):
4077    pass
4078
4079
4080class IntDiv(Binary):
4081    pass
4082
4083
4084class Is(Binary, Predicate):
4085    pass
4086
4087
4088class Kwarg(Binary):
4089    """Kwarg in special functions like func(kwarg => y)."""
4090
4091
4092class Like(Binary, Predicate):
4093    pass
4094
4095
4096class LikeAny(Binary, Predicate):
4097    pass
4098
4099
4100class LT(Binary, Predicate):
4101    pass
4102
4103
4104class LTE(Binary, Predicate):
4105    pass
4106
4107
4108class Mod(Binary):
4109    pass
4110
4111
4112class Mul(Binary):
4113    pass
4114
4115
4116class NEQ(Binary, Predicate):
4117    pass
4118
4119
4120# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4121class Operator(Binary):
4122    arg_types = {"this": True, "operator": True, "expression": True}
4123
4124
4125class SimilarTo(Binary, Predicate):
4126    pass
4127
4128
4129class Slice(Binary):
4130    arg_types = {"this": False, "expression": False}
4131
4132
4133class Sub(Binary):
4134    pass
4135
4136
4137# Unary Expressions
4138# (NOT a)
4139class Unary(Condition):
4140    pass
4141
4142
4143class BitwiseNot(Unary):
4144    pass
4145
4146
4147class Not(Unary):
4148    pass
4149
4150
4151class Paren(Unary):
4152    arg_types = {"this": True, "with": False}
4153
4154    @property
4155    def output_name(self) -> str:
4156        return self.this.name
4157
4158
4159class Neg(Unary):
4160    pass
4161
4162
4163class Alias(Expression):
4164    arg_types = {"this": True, "alias": False}
4165
4166    @property
4167    def output_name(self) -> str:
4168        return self.alias
4169
4170
4171# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4172# other dialects require identifiers. This enables us to transpile between them easily.
4173class PivotAlias(Alias):
4174    pass
4175
4176
4177class Aliases(Expression):
4178    arg_types = {"this": True, "expressions": True}
4179
4180    @property
4181    def aliases(self):
4182        return self.expressions
4183
4184
4185# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4186class AtIndex(Expression):
4187    arg_types = {"this": True, "expression": True}
4188
4189
4190class AtTimeZone(Expression):
4191    arg_types = {"this": True, "zone": True}
4192
4193
4194class FromTimeZone(Expression):
4195    arg_types = {"this": True, "zone": True}
4196
4197
4198class Between(Predicate):
4199    arg_types = {"this": True, "low": True, "high": True}
4200
4201
4202class Bracket(Condition):
4203    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4204    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4205
4206    @property
4207    def output_name(self) -> str:
4208        if len(self.expressions) == 1:
4209            return self.expressions[0].output_name
4210
4211        return super().output_name
4212
4213
4214class Distinct(Expression):
4215    arg_types = {"expressions": False, "on": False}
4216
4217
4218class In(Predicate):
4219    arg_types = {
4220        "this": True,
4221        "expressions": False,
4222        "query": False,
4223        "unnest": False,
4224        "field": False,
4225        "is_global": False,
4226    }
4227
4228
4229# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4230class ForIn(Expression):
4231    arg_types = {"this": True, "expression": True}
4232
4233
4234class TimeUnit(Expression):
4235    """Automatically converts unit arg into a var."""
4236
4237    arg_types = {"unit": False}
4238
4239    UNABBREVIATED_UNIT_NAME = {
4240        "D": "DAY",
4241        "H": "HOUR",
4242        "M": "MINUTE",
4243        "MS": "MILLISECOND",
4244        "NS": "NANOSECOND",
4245        "Q": "QUARTER",
4246        "S": "SECOND",
4247        "US": "MICROSECOND",
4248        "W": "WEEK",
4249        "Y": "YEAR",
4250    }
4251
4252    VAR_LIKE = (Column, Literal, Var)
4253
4254    def __init__(self, **args):
4255        unit = args.get("unit")
4256        if isinstance(unit, self.VAR_LIKE):
4257            args["unit"] = Var(
4258                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4259            )
4260        elif isinstance(unit, Week):
4261            unit.set("this", Var(this=unit.this.name.upper()))
4262
4263        super().__init__(**args)
4264
4265    @property
4266    def unit(self) -> t.Optional[Var]:
4267        return self.args.get("unit")
4268
4269
4270class IntervalOp(TimeUnit):
4271    arg_types = {"unit": True, "expression": True}
4272
4273    def interval(self):
4274        return Interval(
4275            this=self.expression.copy(),
4276            unit=self.unit.copy(),
4277        )
4278
4279
4280# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4281# https://trino.io/docs/current/language/types.html#interval-day-to-second
4282# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4283class IntervalSpan(DataType):
4284    arg_types = {"this": True, "expression": True}
4285
4286
4287class Interval(TimeUnit):
4288    arg_types = {"this": False, "unit": False}
4289
4290
4291class IgnoreNulls(Expression):
4292    pass
4293
4294
4295class RespectNulls(Expression):
4296    pass
4297
4298
4299# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4300class HavingMax(Expression):
4301    arg_types = {"this": True, "expression": True, "max": True}
4302
4303
4304# Functions
4305class Func(Condition):
4306    """
4307    The base class for all function expressions.
4308
4309    Attributes:
4310        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4311            treated as a variable length argument and the argument's value will be stored as a list.
4312        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4313            function expression. These values are used to map this node to a name during parsing as
4314            well as to provide the function's name during SQL string generation. By default the SQL
4315            name is set to the expression's class name transformed to snake case.
4316    """
4317
4318    is_var_len_args = False
4319
4320    @classmethod
4321    def from_arg_list(cls, args):
4322        if cls.is_var_len_args:
4323            all_arg_keys = list(cls.arg_types)
4324            # If this function supports variable length argument treat the last argument as such.
4325            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4326            num_non_var = len(non_var_len_arg_keys)
4327
4328            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4329            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4330        else:
4331            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4332
4333        return cls(**args_dict)
4334
4335    @classmethod
4336    def sql_names(cls):
4337        if cls is Func:
4338            raise NotImplementedError(
4339                "SQL name is only supported by concrete function implementations"
4340            )
4341        if "_sql_names" not in cls.__dict__:
4342            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4343        return cls._sql_names
4344
4345    @classmethod
4346    def sql_name(cls):
4347        return cls.sql_names()[0]
4348
4349    @classmethod
4350    def default_parser_mappings(cls):
4351        return {name: cls.from_arg_list for name in cls.sql_names()}
4352
4353
4354class AggFunc(Func):
4355    pass
4356
4357
4358class ParameterizedAgg(AggFunc):
4359    arg_types = {"this": True, "expressions": True, "params": True}
4360
4361
4362class Abs(Func):
4363    pass
4364
4365
4366class ArgMax(AggFunc):
4367    arg_types = {"this": True, "expression": True, "count": False}
4368    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4369
4370
4371class ArgMin(AggFunc):
4372    arg_types = {"this": True, "expression": True, "count": False}
4373    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4374
4375
4376class ApproxTopK(AggFunc):
4377    arg_types = {"this": True, "expression": False, "counters": False}
4378
4379
4380class Flatten(Func):
4381    pass
4382
4383
4384# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4385class Transform(Func):
4386    arg_types = {"this": True, "expression": True}
4387
4388
4389class Anonymous(Func):
4390    arg_types = {"this": True, "expressions": False}
4391    is_var_len_args = True
4392
4393
4394class AnonymousAggFunc(AggFunc):
4395    arg_types = {"this": True, "expressions": False}
4396    is_var_len_args = True
4397
4398
4399# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4400class CombinedAggFunc(AnonymousAggFunc):
4401    arg_types = {"this": True, "expressions": False, "parts": True}
4402
4403
4404class CombinedParameterizedAgg(ParameterizedAgg):
4405    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4406
4407
4408# https://docs.snowflake.com/en/sql-reference/functions/hll
4409# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4410class Hll(AggFunc):
4411    arg_types = {"this": True, "expressions": False}
4412    is_var_len_args = True
4413
4414
4415class ApproxDistinct(AggFunc):
4416    arg_types = {"this": True, "accuracy": False}
4417    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4418
4419
4420class Array(Func):
4421    arg_types = {"expressions": False}
4422    is_var_len_args = True
4423
4424
4425# https://docs.snowflake.com/en/sql-reference/functions/to_array
4426class ToArray(Func):
4427    pass
4428
4429
4430# https://docs.snowflake.com/en/sql-reference/functions/to_char
4431# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4432class ToChar(Func):
4433    arg_types = {"this": True, "format": False, "nlsparam": False}
4434
4435
4436class GenerateSeries(Func):
4437    arg_types = {"start": True, "end": True, "step": False}
4438
4439
4440class ArrayAgg(AggFunc):
4441    pass
4442
4443
4444class ArrayUniqueAgg(AggFunc):
4445    pass
4446
4447
4448class ArrayAll(Func):
4449    arg_types = {"this": True, "expression": True}
4450
4451
4452# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4453class ArrayAny(Func):
4454    arg_types = {"this": True, "expression": True}
4455
4456
4457class ArrayConcat(Func):
4458    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4459    arg_types = {"this": True, "expressions": False}
4460    is_var_len_args = True
4461
4462
4463class ArrayContains(Binary, Func):
4464    pass
4465
4466
4467class ArrayContained(Binary):
4468    pass
4469
4470
4471class ArrayFilter(Func):
4472    arg_types = {"this": True, "expression": True}
4473    _sql_names = ["FILTER", "ARRAY_FILTER"]
4474
4475
4476class ArrayJoin(Func):
4477    arg_types = {"this": True, "expression": True, "null": False}
4478
4479
4480class ArrayOverlaps(Binary, Func):
4481    pass
4482
4483
4484class ArraySize(Func):
4485    arg_types = {"this": True, "expression": False}
4486    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4487
4488
4489class ArraySort(Func):
4490    arg_types = {"this": True, "expression": False}
4491
4492
4493class ArraySum(Func):
4494    arg_types = {"this": True, "expression": False}
4495
4496
4497class ArrayUnionAgg(AggFunc):
4498    pass
4499
4500
4501class Avg(AggFunc):
4502    pass
4503
4504
4505class AnyValue(AggFunc):
4506    pass
4507
4508
4509class Lag(AggFunc):
4510    arg_types = {"this": True, "offset": False, "default": False}
4511
4512
4513class Lead(AggFunc):
4514    arg_types = {"this": True, "offset": False, "default": False}
4515
4516
4517# some dialects have a distinction between first and first_value, usually first is an aggregate func
4518# and first_value is a window func
4519class First(AggFunc):
4520    pass
4521
4522
4523class Last(AggFunc):
4524    pass
4525
4526
4527class FirstValue(AggFunc):
4528    pass
4529
4530
4531class LastValue(AggFunc):
4532    pass
4533
4534
4535class NthValue(AggFunc):
4536    arg_types = {"this": True, "offset": True}
4537
4538
4539class Case(Func):
4540    arg_types = {"this": False, "ifs": True, "default": False}
4541
4542    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4543        instance = maybe_copy(self, copy)
4544        instance.append(
4545            "ifs",
4546            If(
4547                this=maybe_parse(condition, copy=copy, **opts),
4548                true=maybe_parse(then, copy=copy, **opts),
4549            ),
4550        )
4551        return instance
4552
4553    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4554        instance = maybe_copy(self, copy)
4555        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4556        return instance
4557
4558
4559class Cast(Func):
4560    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4561
4562    @property
4563    def name(self) -> str:
4564        return self.this.name
4565
4566    @property
4567    def to(self) -> DataType:
4568        return self.args["to"]
4569
4570    @property
4571    def output_name(self) -> str:
4572        return self.name
4573
4574    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4575        """
4576        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4577        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4578        array<int> != array<float>.
4579
4580        Args:
4581            dtypes: the data types to compare this Cast's DataType to.
4582
4583        Returns:
4584            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4585        """
4586        return self.to.is_type(*dtypes)
4587
4588
4589class TryCast(Cast):
4590    pass
4591
4592
4593class CastToStrType(Func):
4594    arg_types = {"this": True, "to": True}
4595
4596
4597class Collate(Binary, Func):
4598    pass
4599
4600
4601class Ceil(Func):
4602    arg_types = {"this": True, "decimals": False}
4603    _sql_names = ["CEIL", "CEILING"]
4604
4605
4606class Coalesce(Func):
4607    arg_types = {"this": True, "expressions": False}
4608    is_var_len_args = True
4609    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4610
4611
4612class Chr(Func):
4613    arg_types = {"this": True, "charset": False, "expressions": False}
4614    is_var_len_args = True
4615    _sql_names = ["CHR", "CHAR"]
4616
4617
4618class Concat(Func):
4619    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4620    is_var_len_args = True
4621
4622
4623class ConcatWs(Concat):
4624    _sql_names = ["CONCAT_WS"]
4625
4626
4627class Count(AggFunc):
4628    arg_types = {"this": False, "expressions": False}
4629    is_var_len_args = True
4630
4631
4632class CountIf(AggFunc):
4633    _sql_names = ["COUNT_IF", "COUNTIF"]
4634
4635
4636# cube root
4637class Cbrt(Func):
4638    pass
4639
4640
4641class CurrentDate(Func):
4642    arg_types = {"this": False}
4643
4644
4645class CurrentDatetime(Func):
4646    arg_types = {"this": False}
4647
4648
4649class CurrentTime(Func):
4650    arg_types = {"this": False}
4651
4652
4653class CurrentTimestamp(Func):
4654    arg_types = {"this": False, "transaction": False}
4655
4656
4657class CurrentUser(Func):
4658    arg_types = {"this": False}
4659
4660
4661class DateAdd(Func, IntervalOp):
4662    arg_types = {"this": True, "expression": True, "unit": False}
4663
4664
4665class DateSub(Func, IntervalOp):
4666    arg_types = {"this": True, "expression": True, "unit": False}
4667
4668
4669class DateDiff(Func, TimeUnit):
4670    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4671    arg_types = {"this": True, "expression": True, "unit": False}
4672
4673
4674class DateTrunc(Func):
4675    arg_types = {"unit": True, "this": True, "zone": False}
4676
4677    def __init__(self, **args):
4678        unit = args.get("unit")
4679        if isinstance(unit, TimeUnit.VAR_LIKE):
4680            args["unit"] = Literal.string(
4681                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4682            )
4683        elif isinstance(unit, Week):
4684            unit.set("this", Literal.string(unit.this.name.upper()))
4685
4686        super().__init__(**args)
4687
4688    @property
4689    def unit(self) -> Expression:
4690        return self.args["unit"]
4691
4692
4693class DatetimeAdd(Func, IntervalOp):
4694    arg_types = {"this": True, "expression": True, "unit": False}
4695
4696
4697class DatetimeSub(Func, IntervalOp):
4698    arg_types = {"this": True, "expression": True, "unit": False}
4699
4700
4701class DatetimeDiff(Func, TimeUnit):
4702    arg_types = {"this": True, "expression": True, "unit": False}
4703
4704
4705class DatetimeTrunc(Func, TimeUnit):
4706    arg_types = {"this": True, "unit": True, "zone": False}
4707
4708
4709class DayOfWeek(Func):
4710    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4711
4712
4713class DayOfMonth(Func):
4714    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4715
4716
4717class DayOfYear(Func):
4718    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4719
4720
4721class ToDays(Func):
4722    pass
4723
4724
4725class WeekOfYear(Func):
4726    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4727
4728
4729class MonthsBetween(Func):
4730    arg_types = {"this": True, "expression": True, "roundoff": False}
4731
4732
4733class LastDay(Func, TimeUnit):
4734    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4735    arg_types = {"this": True, "unit": False}
4736
4737
4738class Extract(Func):
4739    arg_types = {"this": True, "expression": True}
4740
4741
4742class Timestamp(Func):
4743    arg_types = {"this": False, "expression": False, "with_tz": False}
4744
4745
4746class TimestampAdd(Func, TimeUnit):
4747    arg_types = {"this": True, "expression": True, "unit": False}
4748
4749
4750class TimestampSub(Func, TimeUnit):
4751    arg_types = {"this": True, "expression": True, "unit": False}
4752
4753
4754class TimestampDiff(Func, TimeUnit):
4755    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
4756    arg_types = {"this": True, "expression": True, "unit": False}
4757
4758
4759class TimestampTrunc(Func, TimeUnit):
4760    arg_types = {"this": True, "unit": True, "zone": False}
4761
4762
4763class TimeAdd(Func, TimeUnit):
4764    arg_types = {"this": True, "expression": True, "unit": False}
4765
4766
4767class TimeSub(Func, TimeUnit):
4768    arg_types = {"this": True, "expression": True, "unit": False}
4769
4770
4771class TimeDiff(Func, TimeUnit):
4772    arg_types = {"this": True, "expression": True, "unit": False}
4773
4774
4775class TimeTrunc(Func, TimeUnit):
4776    arg_types = {"this": True, "unit": True, "zone": False}
4777
4778
4779class DateFromParts(Func):
4780    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4781    arg_types = {"year": True, "month": True, "day": True}
4782
4783
4784class TimeFromParts(Func):
4785    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4786    arg_types = {
4787        "hour": True,
4788        "min": True,
4789        "sec": True,
4790        "nano": False,
4791        "fractions": False,
4792        "precision": False,
4793    }
4794
4795
4796class DateStrToDate(Func):
4797    pass
4798
4799
4800class DateToDateStr(Func):
4801    pass
4802
4803
4804class DateToDi(Func):
4805    pass
4806
4807
4808# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4809class Date(Func):
4810    arg_types = {"this": False, "zone": False, "expressions": False}
4811    is_var_len_args = True
4812
4813
4814class Day(Func):
4815    pass
4816
4817
4818class Decode(Func):
4819    arg_types = {"this": True, "charset": True, "replace": False}
4820
4821
4822class DiToDate(Func):
4823    pass
4824
4825
4826class Encode(Func):
4827    arg_types = {"this": True, "charset": True}
4828
4829
4830class Exp(Func):
4831    pass
4832
4833
4834# https://docs.snowflake.com/en/sql-reference/functions/flatten
4835class Explode(Func):
4836    arg_types = {"this": True, "expressions": False}
4837    is_var_len_args = True
4838
4839
4840class ExplodeOuter(Explode):
4841    pass
4842
4843
4844class Posexplode(Explode):
4845    pass
4846
4847
4848class PosexplodeOuter(Posexplode, ExplodeOuter):
4849    pass
4850
4851
4852class Floor(Func):
4853    arg_types = {"this": True, "decimals": False}
4854
4855
4856class FromBase64(Func):
4857    pass
4858
4859
4860class ToBase64(Func):
4861    pass
4862
4863
4864class Greatest(Func):
4865    arg_types = {"this": True, "expressions": False}
4866    is_var_len_args = True
4867
4868
4869class GroupConcat(AggFunc):
4870    arg_types = {"this": True, "separator": False}
4871
4872
4873class Hex(Func):
4874    pass
4875
4876
4877class Xor(Connector, Func):
4878    arg_types = {"this": False, "expression": False, "expressions": False}
4879
4880
4881class If(Func):
4882    arg_types = {"this": True, "true": True, "false": False}
4883    _sql_names = ["IF", "IIF"]
4884
4885
4886class Nullif(Func):
4887    arg_types = {"this": True, "expression": True}
4888
4889
4890class Initcap(Func):
4891    arg_types = {"this": True, "expression": False}
4892
4893
4894class IsNan(Func):
4895    _sql_names = ["IS_NAN", "ISNAN"]
4896
4897
4898class IsInf(Func):
4899    _sql_names = ["IS_INF", "ISINF"]
4900
4901
4902class JSONPath(Expression):
4903    arg_types = {"expressions": True}
4904
4905    @property
4906    def output_name(self) -> str:
4907        last_segment = self.expressions[-1].this
4908        return last_segment if isinstance(last_segment, str) else ""
4909
4910
4911class JSONPathPart(Expression):
4912    arg_types = {}
4913
4914
4915class JSONPathFilter(JSONPathPart):
4916    arg_types = {"this": True}
4917
4918
4919class JSONPathKey(JSONPathPart):
4920    arg_types = {"this": True}
4921
4922
4923class JSONPathRecursive(JSONPathPart):
4924    arg_types = {"this": False}
4925
4926
4927class JSONPathRoot(JSONPathPart):
4928    pass
4929
4930
4931class JSONPathScript(JSONPathPart):
4932    arg_types = {"this": True}
4933
4934
4935class JSONPathSlice(JSONPathPart):
4936    arg_types = {"start": False, "end": False, "step": False}
4937
4938
4939class JSONPathSelector(JSONPathPart):
4940    arg_types = {"this": True}
4941
4942
4943class JSONPathSubscript(JSONPathPart):
4944    arg_types = {"this": True}
4945
4946
4947class JSONPathUnion(JSONPathPart):
4948    arg_types = {"expressions": True}
4949
4950
4951class JSONPathWildcard(JSONPathPart):
4952    pass
4953
4954
4955class FormatJson(Expression):
4956    pass
4957
4958
4959class JSONKeyValue(Expression):
4960    arg_types = {"this": True, "expression": True}
4961
4962
4963class JSONObject(Func):
4964    arg_types = {
4965        "expressions": False,
4966        "null_handling": False,
4967        "unique_keys": False,
4968        "return_type": False,
4969        "encoding": False,
4970    }
4971
4972
4973class JSONObjectAgg(AggFunc):
4974    arg_types = {
4975        "expressions": False,
4976        "null_handling": False,
4977        "unique_keys": False,
4978        "return_type": False,
4979        "encoding": False,
4980    }
4981
4982
4983# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4984class JSONArray(Func):
4985    arg_types = {
4986        "expressions": True,
4987        "null_handling": False,
4988        "return_type": False,
4989        "strict": False,
4990    }
4991
4992
4993# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4994class JSONArrayAgg(Func):
4995    arg_types = {
4996        "this": True,
4997        "order": False,
4998        "null_handling": False,
4999        "return_type": False,
5000        "strict": False,
5001    }
5002
5003
5004# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5005# Note: parsing of JSON column definitions is currently incomplete.
5006class JSONColumnDef(Expression):
5007    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5008
5009
5010class JSONSchema(Expression):
5011    arg_types = {"expressions": True}
5012
5013
5014# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5015class JSONTable(Func):
5016    arg_types = {
5017        "this": True,
5018        "schema": True,
5019        "path": False,
5020        "error_handling": False,
5021        "empty_handling": False,
5022    }
5023
5024
5025class OpenJSONColumnDef(Expression):
5026    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5027
5028
5029class OpenJSON(Func):
5030    arg_types = {"this": True, "path": False, "expressions": False}
5031
5032
5033class JSONBContains(Binary):
5034    _sql_names = ["JSONB_CONTAINS"]
5035
5036
5037class JSONExtract(Binary, Func):
5038    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5039    _sql_names = ["JSON_EXTRACT"]
5040    is_var_len_args = True
5041
5042    @property
5043    def output_name(self) -> str:
5044        return self.expression.output_name if not self.expressions else ""
5045
5046
5047class JSONExtractScalar(Binary, Func):
5048    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5049    _sql_names = ["JSON_EXTRACT_SCALAR"]
5050    is_var_len_args = True
5051
5052    @property
5053    def output_name(self) -> str:
5054        return self.expression.output_name
5055
5056
5057class JSONBExtract(Binary, Func):
5058    _sql_names = ["JSONB_EXTRACT"]
5059
5060
5061class JSONBExtractScalar(Binary, Func):
5062    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5063
5064
5065class JSONFormat(Func):
5066    arg_types = {"this": False, "options": False}
5067    _sql_names = ["JSON_FORMAT"]
5068
5069
5070# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5071class JSONArrayContains(Binary, Predicate, Func):
5072    _sql_names = ["JSON_ARRAY_CONTAINS"]
5073
5074
5075class ParseJSON(Func):
5076    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5077    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5078    arg_types = {"this": True, "expressions": False}
5079    is_var_len_args = True
5080
5081
5082class Least(Func):
5083    arg_types = {"this": True, "expressions": False}
5084    is_var_len_args = True
5085
5086
5087class Left(Func):
5088    arg_types = {"this": True, "expression": True}
5089
5090
5091class Right(Func):
5092    arg_types = {"this": True, "expression": True}
5093
5094
5095class Length(Func):
5096    _sql_names = ["LENGTH", "LEN"]
5097
5098
5099class Levenshtein(Func):
5100    arg_types = {
5101        "this": True,
5102        "expression": False,
5103        "ins_cost": False,
5104        "del_cost": False,
5105        "sub_cost": False,
5106    }
5107
5108
5109class Ln(Func):
5110    pass
5111
5112
5113class Log(Func):
5114    arg_types = {"this": True, "expression": False}
5115
5116
5117class Log2(Func):
5118    pass
5119
5120
5121class Log10(Func):
5122    pass
5123
5124
5125class LogicalOr(AggFunc):
5126    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5127
5128
5129class LogicalAnd(AggFunc):
5130    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5131
5132
5133class Lower(Func):
5134    _sql_names = ["LOWER", "LCASE"]
5135
5136
5137class Map(Func):
5138    arg_types = {"keys": False, "values": False}
5139
5140    @property
5141    def keys(self) -> t.List[Expression]:
5142        keys = self.args.get("keys")
5143        return keys.expressions if keys else []
5144
5145    @property
5146    def values(self) -> t.List[Expression]:
5147        values = self.args.get("values")
5148        return values.expressions if values else []
5149
5150
5151class MapFromEntries(Func):
5152    pass
5153
5154
5155class StarMap(Func):
5156    pass
5157
5158
5159class VarMap(Func):
5160    arg_types = {"keys": True, "values": True}
5161    is_var_len_args = True
5162
5163    @property
5164    def keys(self) -> t.List[Expression]:
5165        return self.args["keys"].expressions
5166
5167    @property
5168    def values(self) -> t.List[Expression]:
5169        return self.args["values"].expressions
5170
5171
5172# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5173class MatchAgainst(Func):
5174    arg_types = {"this": True, "expressions": True, "modifier": False}
5175
5176
5177class Max(AggFunc):
5178    arg_types = {"this": True, "expressions": False}
5179    is_var_len_args = True
5180
5181
5182class MD5(Func):
5183    _sql_names = ["MD5"]
5184
5185
5186# Represents the variant of the MD5 function that returns a binary value
5187class MD5Digest(Func):
5188    _sql_names = ["MD5_DIGEST"]
5189
5190
5191class Min(AggFunc):
5192    arg_types = {"this": True, "expressions": False}
5193    is_var_len_args = True
5194
5195
5196class Month(Func):
5197    pass
5198
5199
5200class Nvl2(Func):
5201    arg_types = {"this": True, "true": True, "false": False}
5202
5203
5204# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5205class Predict(Func):
5206    arg_types = {"this": True, "expression": True, "params_struct": False}
5207
5208
5209class Pow(Binary, Func):
5210    _sql_names = ["POWER", "POW"]
5211
5212
5213class PercentileCont(AggFunc):
5214    arg_types = {"this": True, "expression": False}
5215
5216
5217class PercentileDisc(AggFunc):
5218    arg_types = {"this": True, "expression": False}
5219
5220
5221class Quantile(AggFunc):
5222    arg_types = {"this": True, "quantile": True}
5223
5224
5225class ApproxQuantile(Quantile):
5226    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5227
5228
5229class Rand(Func):
5230    _sql_names = ["RAND", "RANDOM"]
5231    arg_types = {"this": False}
5232
5233
5234class Randn(Func):
5235    arg_types = {"this": False}
5236
5237
5238class RangeN(Func):
5239    arg_types = {"this": True, "expressions": True, "each": False}
5240
5241
5242class ReadCSV(Func):
5243    _sql_names = ["READ_CSV"]
5244    is_var_len_args = True
5245    arg_types = {"this": True, "expressions": False}
5246
5247
5248class Reduce(Func):
5249    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5250
5251
5252class RegexpExtract(Func):
5253    arg_types = {
5254        "this": True,
5255        "expression": True,
5256        "position": False,
5257        "occurrence": False,
5258        "parameters": False,
5259        "group": False,
5260    }
5261
5262
5263class RegexpReplace(Func):
5264    arg_types = {
5265        "this": True,
5266        "expression": True,
5267        "replacement": False,
5268        "position": False,
5269        "occurrence": False,
5270        "parameters": False,
5271        "modifiers": False,
5272    }
5273
5274
5275class RegexpLike(Binary, Func):
5276    arg_types = {"this": True, "expression": True, "flag": False}
5277
5278
5279class RegexpILike(Binary, Func):
5280    arg_types = {"this": True, "expression": True, "flag": False}
5281
5282
5283# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5284# limit is the number of times a pattern is applied
5285class RegexpSplit(Func):
5286    arg_types = {"this": True, "expression": True, "limit": False}
5287
5288
5289class Repeat(Func):
5290    arg_types = {"this": True, "times": True}
5291
5292
5293# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5294# tsql third argument function == trunctaion if not 0
5295class Round(Func):
5296    arg_types = {"this": True, "decimals": False, "truncate": False}
5297
5298
5299class RowNumber(Func):
5300    arg_types: t.Dict[str, t.Any] = {}
5301
5302
5303class SafeDivide(Func):
5304    arg_types = {"this": True, "expression": True}
5305
5306
5307class SHA(Func):
5308    _sql_names = ["SHA", "SHA1"]
5309
5310
5311class SHA2(Func):
5312    _sql_names = ["SHA2"]
5313    arg_types = {"this": True, "length": False}
5314
5315
5316class SortArray(Func):
5317    arg_types = {"this": True, "asc": False}
5318
5319
5320class Split(Func):
5321    arg_types = {"this": True, "expression": True, "limit": False}
5322
5323
5324# Start may be omitted in the case of postgres
5325# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5326class Substring(Func):
5327    arg_types = {"this": True, "start": False, "length": False}
5328
5329
5330class StandardHash(Func):
5331    arg_types = {"this": True, "expression": False}
5332
5333
5334class StartsWith(Func):
5335    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5336    arg_types = {"this": True, "expression": True}
5337
5338
5339class StrPosition(Func):
5340    arg_types = {
5341        "this": True,
5342        "substr": True,
5343        "position": False,
5344        "instance": False,
5345    }
5346
5347
5348class StrToDate(Func):
5349    arg_types = {"this": True, "format": True}
5350
5351
5352class StrToTime(Func):
5353    arg_types = {"this": True, "format": True, "zone": False}
5354
5355
5356# Spark allows unix_timestamp()
5357# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5358class StrToUnix(Func):
5359    arg_types = {"this": False, "format": False}
5360
5361
5362# https://prestodb.io/docs/current/functions/string.html
5363# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5364class StrToMap(Func):
5365    arg_types = {
5366        "this": True,
5367        "pair_delim": False,
5368        "key_value_delim": False,
5369        "duplicate_resolution_callback": False,
5370    }
5371
5372
5373class NumberToStr(Func):
5374    arg_types = {"this": True, "format": True, "culture": False}
5375
5376
5377class FromBase(Func):
5378    arg_types = {"this": True, "expression": True}
5379
5380
5381class Struct(Func):
5382    arg_types = {"expressions": False}
5383    is_var_len_args = True
5384
5385
5386class StructExtract(Func):
5387    arg_types = {"this": True, "expression": True}
5388
5389
5390# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5391# https://docs.snowflake.com/en/sql-reference/functions/insert
5392class Stuff(Func):
5393    _sql_names = ["STUFF", "INSERT"]
5394    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5395
5396
5397class Sum(AggFunc):
5398    pass
5399
5400
5401class Sqrt(Func):
5402    pass
5403
5404
5405class Stddev(AggFunc):
5406    pass
5407
5408
5409class StddevPop(AggFunc):
5410    pass
5411
5412
5413class StddevSamp(AggFunc):
5414    pass
5415
5416
5417class TimeToStr(Func):
5418    arg_types = {"this": True, "format": True, "culture": False}
5419
5420
5421class TimeToTimeStr(Func):
5422    pass
5423
5424
5425class TimeToUnix(Func):
5426    pass
5427
5428
5429class TimeStrToDate(Func):
5430    pass
5431
5432
5433class TimeStrToTime(Func):
5434    pass
5435
5436
5437class TimeStrToUnix(Func):
5438    pass
5439
5440
5441class Trim(Func):
5442    arg_types = {
5443        "this": True,
5444        "expression": False,
5445        "position": False,
5446        "collation": False,
5447    }
5448
5449
5450class TsOrDsAdd(Func, TimeUnit):
5451    # return_type is used to correctly cast the arguments of this expression when transpiling it
5452    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5453
5454    @property
5455    def return_type(self) -> DataType:
5456        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5457
5458
5459class TsOrDsDiff(Func, TimeUnit):
5460    arg_types = {"this": True, "expression": True, "unit": False}
5461
5462
5463class TsOrDsToDateStr(Func):
5464    pass
5465
5466
5467class TsOrDsToDate(Func):
5468    arg_types = {"this": True, "format": False}
5469
5470
5471class TsOrDsToTime(Func):
5472    pass
5473
5474
5475class TsOrDiToDi(Func):
5476    pass
5477
5478
5479class Unhex(Func):
5480    pass
5481
5482
5483# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5484class UnixDate(Func):
5485    pass
5486
5487
5488class UnixToStr(Func):
5489    arg_types = {"this": True, "format": False}
5490
5491
5492# https://prestodb.io/docs/current/functions/datetime.html
5493# presto has weird zone/hours/minutes
5494class UnixToTime(Func):
5495    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5496
5497    SECONDS = Literal.number(0)
5498    DECIS = Literal.number(1)
5499    CENTIS = Literal.number(2)
5500    MILLIS = Literal.number(3)
5501    DECIMILLIS = Literal.number(4)
5502    CENTIMILLIS = Literal.number(5)
5503    MICROS = Literal.number(6)
5504    DECIMICROS = Literal.number(7)
5505    CENTIMICROS = Literal.number(8)
5506    NANOS = Literal.number(9)
5507
5508
5509class UnixToTimeStr(Func):
5510    pass
5511
5512
5513class TimestampFromParts(Func):
5514    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5515    arg_types = {
5516        "year": True,
5517        "month": True,
5518        "day": True,
5519        "hour": True,
5520        "min": True,
5521        "sec": True,
5522        "nano": False,
5523        "zone": False,
5524        "milli": False,
5525    }
5526
5527
5528class Upper(Func):
5529    _sql_names = ["UPPER", "UCASE"]
5530
5531
5532class Variance(AggFunc):
5533    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5534
5535
5536class VariancePop(AggFunc):
5537    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5538
5539
5540class Week(Func):
5541    arg_types = {"this": True, "mode": False}
5542
5543
5544class XMLTable(Func):
5545    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5546
5547
5548class Year(Func):
5549    pass
5550
5551
5552class Use(Expression):
5553    arg_types = {"this": True, "kind": False}
5554
5555
5556class Merge(Expression):
5557    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5558
5559
5560class When(Func):
5561    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5562
5563
5564# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5565# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5566class NextValueFor(Func):
5567    arg_types = {"this": True, "order": False}
5568
5569
5570def _norm_arg(arg):
5571    return arg.lower() if type(arg) is str else arg
5572
5573
5574ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5575FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5576
5577JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
5578
5579
5580# Helpers
5581@t.overload
5582def maybe_parse(
5583    sql_or_expression: ExpOrStr,
5584    *,
5585    into: t.Type[E],
5586    dialect: DialectType = None,
5587    prefix: t.Optional[str] = None,
5588    copy: bool = False,
5589    **opts,
5590) -> E:
5591    ...
5592
5593
5594@t.overload
5595def maybe_parse(
5596    sql_or_expression: str | E,
5597    *,
5598    into: t.Optional[IntoType] = None,
5599    dialect: DialectType = None,
5600    prefix: t.Optional[str] = None,
5601    copy: bool = False,
5602    **opts,
5603) -> E:
5604    ...
5605
5606
5607def maybe_parse(
5608    sql_or_expression: ExpOrStr,
5609    *,
5610    into: t.Optional[IntoType] = None,
5611    dialect: DialectType = None,
5612    prefix: t.Optional[str] = None,
5613    copy: bool = False,
5614    **opts,
5615) -> Expression:
5616    """Gracefully handle a possible string or expression.
5617
5618    Example:
5619        >>> maybe_parse("1")
5620        Literal(this=1, is_string=False)
5621        >>> maybe_parse(to_identifier("x"))
5622        Identifier(this=x, quoted=False)
5623
5624    Args:
5625        sql_or_expression: the SQL code string or an expression
5626        into: the SQLGlot Expression to parse into
5627        dialect: the dialect used to parse the input expressions (in the case that an
5628            input expression is a SQL string).
5629        prefix: a string to prefix the sql with before it gets parsed
5630            (automatically includes a space)
5631        copy: whether to copy the expression.
5632        **opts: other options to use to parse the input expressions (again, in the case
5633            that an input expression is a SQL string).
5634
5635    Returns:
5636        Expression: the parsed or given expression.
5637    """
5638    if isinstance(sql_or_expression, Expression):
5639        if copy:
5640            return sql_or_expression.copy()
5641        return sql_or_expression
5642
5643    if sql_or_expression is None:
5644        raise ParseError("SQL cannot be None")
5645
5646    import sqlglot
5647
5648    sql = str(sql_or_expression)
5649    if prefix:
5650        sql = f"{prefix} {sql}"
5651
5652    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5653
5654
5655@t.overload
5656def maybe_copy(instance: None, copy: bool = True) -> None:
5657    ...
5658
5659
5660@t.overload
5661def maybe_copy(instance: E, copy: bool = True) -> E:
5662    ...
5663
5664
5665def maybe_copy(instance, copy=True):
5666    return instance.copy() if copy and instance else instance
5667
5668
5669def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5670    """Generate a textual representation of an Expression tree"""
5671    indent = "\n" + ("  " * (level + 1))
5672    delim = f",{indent}"
5673
5674    if isinstance(node, Expression):
5675        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5676
5677        if (node.type or verbose) and not isinstance(node, DataType):
5678            args["_type"] = node.type
5679        if node.comments or verbose:
5680            args["_comments"] = node.comments
5681
5682        if verbose:
5683            args["_id"] = id(node)
5684
5685        # Inline leaves for a more compact representation
5686        if node.is_leaf():
5687            indent = ""
5688            delim = ", "
5689
5690        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5691        return f"{node.__class__.__name__}({indent}{items})"
5692
5693    if isinstance(node, list):
5694        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5695        items = f"{indent}{items}" if items else ""
5696        return f"[{items}]"
5697
5698    # Indent multiline strings to match the current level
5699    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5700
5701
5702def _is_wrong_expression(expression, into):
5703    return isinstance(expression, Expression) and not isinstance(expression, into)
5704
5705
5706def _apply_builder(
5707    expression,
5708    instance,
5709    arg,
5710    copy=True,
5711    prefix=None,
5712    into=None,
5713    dialect=None,
5714    into_arg="this",
5715    **opts,
5716):
5717    if _is_wrong_expression(expression, into):
5718        expression = into(**{into_arg: expression})
5719    instance = maybe_copy(instance, copy)
5720    expression = maybe_parse(
5721        sql_or_expression=expression,
5722        prefix=prefix,
5723        into=into,
5724        dialect=dialect,
5725        **opts,
5726    )
5727    instance.set(arg, expression)
5728    return instance
5729
5730
5731def _apply_child_list_builder(
5732    *expressions,
5733    instance,
5734    arg,
5735    append=True,
5736    copy=True,
5737    prefix=None,
5738    into=None,
5739    dialect=None,
5740    properties=None,
5741    **opts,
5742):
5743    instance = maybe_copy(instance, copy)
5744    parsed = []
5745    for expression in expressions:
5746        if expression is not None:
5747            if _is_wrong_expression(expression, into):
5748                expression = into(expressions=[expression])
5749
5750            expression = maybe_parse(
5751                expression,
5752                into=into,
5753                dialect=dialect,
5754                prefix=prefix,
5755                **opts,
5756            )
5757            parsed.extend(expression.expressions)
5758
5759    existing = instance.args.get(arg)
5760    if append and existing:
5761        parsed = existing.expressions + parsed
5762
5763    child = into(expressions=parsed)
5764    for k, v in (properties or {}).items():
5765        child.set(k, v)
5766    instance.set(arg, child)
5767
5768    return instance
5769
5770
5771def _apply_list_builder(
5772    *expressions,
5773    instance,
5774    arg,
5775    append=True,
5776    copy=True,
5777    prefix=None,
5778    into=None,
5779    dialect=None,
5780    **opts,
5781):
5782    inst = maybe_copy(instance, copy)
5783
5784    expressions = [
5785        maybe_parse(
5786            sql_or_expression=expression,
5787            into=into,
5788            prefix=prefix,
5789            dialect=dialect,
5790            **opts,
5791        )
5792        for expression in expressions
5793        if expression is not None
5794    ]
5795
5796    existing_expressions = inst.args.get(arg)
5797    if append and existing_expressions:
5798        expressions = existing_expressions + expressions
5799
5800    inst.set(arg, expressions)
5801    return inst
5802
5803
5804def _apply_conjunction_builder(
5805    *expressions,
5806    instance,
5807    arg,
5808    into=None,
5809    append=True,
5810    copy=True,
5811    dialect=None,
5812    **opts,
5813):
5814    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5815    if not expressions:
5816        return instance
5817
5818    inst = maybe_copy(instance, copy)
5819
5820    existing = inst.args.get(arg)
5821    if append and existing is not None:
5822        expressions = [existing.this if into else existing] + list(expressions)
5823
5824    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5825
5826    inst.set(arg, into(this=node) if into else node)
5827    return inst
5828
5829
5830def _apply_cte_builder(
5831    instance: E,
5832    alias: ExpOrStr,
5833    as_: ExpOrStr,
5834    recursive: t.Optional[bool] = None,
5835    append: bool = True,
5836    dialect: DialectType = None,
5837    copy: bool = True,
5838    **opts,
5839) -> E:
5840    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5841    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5842    cte = CTE(this=as_expression, alias=alias_expression)
5843    return _apply_child_list_builder(
5844        cte,
5845        instance=instance,
5846        arg="with",
5847        append=append,
5848        copy=copy,
5849        into=With,
5850        properties={"recursive": recursive or False},
5851    )
5852
5853
5854def _combine(
5855    expressions: t.Sequence[t.Optional[ExpOrStr]],
5856    operator: t.Type[Connector],
5857    dialect: DialectType = None,
5858    copy: bool = True,
5859    **opts,
5860) -> Expression:
5861    conditions = [
5862        condition(expression, dialect=dialect, copy=copy, **opts)
5863        for expression in expressions
5864        if expression is not None
5865    ]
5866
5867    this, *rest = conditions
5868    if rest:
5869        this = _wrap(this, Connector)
5870    for expression in rest:
5871        this = operator(this=this, expression=_wrap(expression, Connector))
5872
5873    return this
5874
5875
5876def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5877    return Paren(this=expression) if isinstance(expression, kind) else expression
5878
5879
5880def union(
5881    left: ExpOrStr,
5882    right: ExpOrStr,
5883    distinct: bool = True,
5884    dialect: DialectType = None,
5885    copy: bool = True,
5886    **opts,
5887) -> Union:
5888    """
5889    Initializes a syntax tree from one UNION expression.
5890
5891    Example:
5892        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5893        'SELECT * FROM foo UNION SELECT * FROM bla'
5894
5895    Args:
5896        left: the SQL code string corresponding to the left-hand side.
5897            If an `Expression` instance is passed, it will be used as-is.
5898        right: the SQL code string corresponding to the right-hand side.
5899            If an `Expression` instance is passed, it will be used as-is.
5900        distinct: set the DISTINCT flag if and only if this is true.
5901        dialect: the dialect used to parse the input expression.
5902        copy: whether to copy the expression.
5903        opts: other options to use to parse the input expressions.
5904
5905    Returns:
5906        The new Union instance.
5907    """
5908    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5909    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5910
5911    return Union(this=left, expression=right, distinct=distinct)
5912
5913
5914def intersect(
5915    left: ExpOrStr,
5916    right: ExpOrStr,
5917    distinct: bool = True,
5918    dialect: DialectType = None,
5919    copy: bool = True,
5920    **opts,
5921) -> Intersect:
5922    """
5923    Initializes a syntax tree from one INTERSECT expression.
5924
5925    Example:
5926        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5927        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5928
5929    Args:
5930        left: the SQL code string corresponding to the left-hand side.
5931            If an `Expression` instance is passed, it will be used as-is.
5932        right: the SQL code string corresponding to the right-hand side.
5933            If an `Expression` instance is passed, it will be used as-is.
5934        distinct: set the DISTINCT flag if and only if this is true.
5935        dialect: the dialect used to parse the input expression.
5936        copy: whether to copy the expression.
5937        opts: other options to use to parse the input expressions.
5938
5939    Returns:
5940        The new Intersect instance.
5941    """
5942    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5943    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5944
5945    return Intersect(this=left, expression=right, distinct=distinct)
5946
5947
5948def except_(
5949    left: ExpOrStr,
5950    right: ExpOrStr,
5951    distinct: bool = True,
5952    dialect: DialectType = None,
5953    copy: bool = True,
5954    **opts,
5955) -> Except:
5956    """
5957    Initializes a syntax tree from one EXCEPT expression.
5958
5959    Example:
5960        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5961        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5962
5963    Args:
5964        left: the SQL code string corresponding to the left-hand side.
5965            If an `Expression` instance is passed, it will be used as-is.
5966        right: the SQL code string corresponding to the right-hand side.
5967            If an `Expression` instance is passed, it will be used as-is.
5968        distinct: set the DISTINCT flag if and only if this is true.
5969        dialect: the dialect used to parse the input expression.
5970        copy: whether to copy the expression.
5971        opts: other options to use to parse the input expressions.
5972
5973    Returns:
5974        The new Except instance.
5975    """
5976    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5977    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5978
5979    return Except(this=left, expression=right, distinct=distinct)
5980
5981
5982def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5983    """
5984    Initializes a syntax tree from one or multiple SELECT expressions.
5985
5986    Example:
5987        >>> select("col1", "col2").from_("tbl").sql()
5988        'SELECT col1, col2 FROM tbl'
5989
5990    Args:
5991        *expressions: the SQL code string to parse as the expressions of a
5992            SELECT statement. If an Expression instance is passed, this is used as-is.
5993        dialect: the dialect used to parse the input expressions (in the case that an
5994            input expression is a SQL string).
5995        **opts: other options to use to parse the input expressions (again, in the case
5996            that an input expression is a SQL string).
5997
5998    Returns:
5999        Select: the syntax tree for the SELECT statement.
6000    """
6001    return Select().select(*expressions, dialect=dialect, **opts)
6002
6003
6004def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6005    """
6006    Initializes a syntax tree from a FROM expression.
6007
6008    Example:
6009        >>> from_("tbl").select("col1", "col2").sql()
6010        'SELECT col1, col2 FROM tbl'
6011
6012    Args:
6013        *expression: the SQL code string to parse as the FROM expressions of a
6014            SELECT statement. If an Expression instance is passed, this is used as-is.
6015        dialect: the dialect used to parse the input expression (in the case that the
6016            input expression is a SQL string).
6017        **opts: other options to use to parse the input expressions (again, in the case
6018            that the input expression is a SQL string).
6019
6020    Returns:
6021        Select: the syntax tree for the SELECT statement.
6022    """
6023    return Select().from_(expression, dialect=dialect, **opts)
6024
6025
6026def update(
6027    table: str | Table,
6028    properties: dict,
6029    where: t.Optional[ExpOrStr] = None,
6030    from_: t.Optional[ExpOrStr] = None,
6031    dialect: DialectType = None,
6032    **opts,
6033) -> Update:
6034    """
6035    Creates an update statement.
6036
6037    Example:
6038        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6039        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6040
6041    Args:
6042        *properties: dictionary of properties to set which are
6043            auto converted to sql objects eg None -> NULL
6044        where: sql conditional parsed into a WHERE statement
6045        from_: sql statement parsed into a FROM statement
6046        dialect: the dialect used to parse the input expressions.
6047        **opts: other options to use to parse the input expressions.
6048
6049    Returns:
6050        Update: the syntax tree for the UPDATE statement.
6051    """
6052    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6053    update_expr.set(
6054        "expressions",
6055        [
6056            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6057            for k, v in properties.items()
6058        ],
6059    )
6060    if from_:
6061        update_expr.set(
6062            "from",
6063            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6064        )
6065    if isinstance(where, Condition):
6066        where = Where(this=where)
6067    if where:
6068        update_expr.set(
6069            "where",
6070            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6071        )
6072    return update_expr
6073
6074
6075def delete(
6076    table: ExpOrStr,
6077    where: t.Optional[ExpOrStr] = None,
6078    returning: t.Optional[ExpOrStr] = None,
6079    dialect: DialectType = None,
6080    **opts,
6081) -> Delete:
6082    """
6083    Builds a delete statement.
6084
6085    Example:
6086        >>> delete("my_table", where="id > 1").sql()
6087        'DELETE FROM my_table WHERE id > 1'
6088
6089    Args:
6090        where: sql conditional parsed into a WHERE statement
6091        returning: sql conditional parsed into a RETURNING statement
6092        dialect: the dialect used to parse the input expressions.
6093        **opts: other options to use to parse the input expressions.
6094
6095    Returns:
6096        Delete: the syntax tree for the DELETE statement.
6097    """
6098    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6099    if where:
6100        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6101    if returning:
6102        delete_expr = t.cast(
6103            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6104        )
6105    return delete_expr
6106
6107
6108def insert(
6109    expression: ExpOrStr,
6110    into: ExpOrStr,
6111    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6112    overwrite: t.Optional[bool] = None,
6113    returning: t.Optional[ExpOrStr] = None,
6114    dialect: DialectType = None,
6115    copy: bool = True,
6116    **opts,
6117) -> Insert:
6118    """
6119    Builds an INSERT statement.
6120
6121    Example:
6122        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6123        'INSERT INTO tbl VALUES (1, 2, 3)'
6124
6125    Args:
6126        expression: the sql string or expression of the INSERT statement
6127        into: the tbl to insert data to.
6128        columns: optionally the table's column names.
6129        overwrite: whether to INSERT OVERWRITE or not.
6130        returning: sql conditional parsed into a RETURNING statement
6131        dialect: the dialect used to parse the input expressions.
6132        copy: whether to copy the expression.
6133        **opts: other options to use to parse the input expressions.
6134
6135    Returns:
6136        Insert: the syntax tree for the INSERT statement.
6137    """
6138    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6139    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6140
6141    if columns:
6142        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6143
6144    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6145
6146    if returning:
6147        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6148
6149    return insert
6150
6151
6152def condition(
6153    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6154) -> Condition:
6155    """
6156    Initialize a logical condition expression.
6157
6158    Example:
6159        >>> condition("x=1").sql()
6160        'x = 1'
6161
6162        This is helpful for composing larger logical syntax trees:
6163        >>> where = condition("x=1")
6164        >>> where = where.and_("y=1")
6165        >>> Select().from_("tbl").select("*").where(where).sql()
6166        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6167
6168    Args:
6169        *expression: the SQL code string to parse.
6170            If an Expression instance is passed, this is used as-is.
6171        dialect: the dialect used to parse the input expression (in the case that the
6172            input expression is a SQL string).
6173        copy: Whether to copy `expression` (only applies to expressions).
6174        **opts: other options to use to parse the input expressions (again, in the case
6175            that the input expression is a SQL string).
6176
6177    Returns:
6178        The new Condition instance
6179    """
6180    return maybe_parse(
6181        expression,
6182        into=Condition,
6183        dialect=dialect,
6184        copy=copy,
6185        **opts,
6186    )
6187
6188
6189def and_(
6190    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6191) -> Condition:
6192    """
6193    Combine multiple conditions with an AND logical operator.
6194
6195    Example:
6196        >>> and_("x=1", and_("y=1", "z=1")).sql()
6197        'x = 1 AND (y = 1 AND z = 1)'
6198
6199    Args:
6200        *expressions: the SQL code strings to parse.
6201            If an Expression instance is passed, this is used as-is.
6202        dialect: the dialect used to parse the input expression.
6203        copy: whether to copy `expressions` (only applies to Expressions).
6204        **opts: other options to use to parse the input expressions.
6205
6206    Returns:
6207        And: the new condition
6208    """
6209    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6210
6211
6212def or_(
6213    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6214) -> Condition:
6215    """
6216    Combine multiple conditions with an OR logical operator.
6217
6218    Example:
6219        >>> or_("x=1", or_("y=1", "z=1")).sql()
6220        'x = 1 OR (y = 1 OR z = 1)'
6221
6222    Args:
6223        *expressions: the SQL code strings to parse.
6224            If an Expression instance is passed, this is used as-is.
6225        dialect: the dialect used to parse the input expression.
6226        copy: whether to copy `expressions` (only applies to Expressions).
6227        **opts: other options to use to parse the input expressions.
6228
6229    Returns:
6230        Or: the new condition
6231    """
6232    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6233
6234
6235def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6236    """
6237    Wrap a condition with a NOT operator.
6238
6239    Example:
6240        >>> not_("this_suit='black'").sql()
6241        "NOT this_suit = 'black'"
6242
6243    Args:
6244        expression: the SQL code string to parse.
6245            If an Expression instance is passed, this is used as-is.
6246        dialect: the dialect used to parse the input expression.
6247        copy: whether to copy the expression or not.
6248        **opts: other options to use to parse the input expressions.
6249
6250    Returns:
6251        The new condition.
6252    """
6253    this = condition(
6254        expression,
6255        dialect=dialect,
6256        copy=copy,
6257        **opts,
6258    )
6259    return Not(this=_wrap(this, Connector))
6260
6261
6262def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6263    """
6264    Wrap an expression in parentheses.
6265
6266    Example:
6267        >>> paren("5 + 3").sql()
6268        '(5 + 3)'
6269
6270    Args:
6271        expression: the SQL code string to parse.
6272            If an Expression instance is passed, this is used as-is.
6273        copy: whether to copy the expression or not.
6274
6275    Returns:
6276        The wrapped expression.
6277    """
6278    return Paren(this=maybe_parse(expression, copy=copy))
6279
6280
6281SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6282
6283
6284@t.overload
6285def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
6286    ...
6287
6288
6289@t.overload
6290def to_identifier(
6291    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6292) -> Identifier:
6293    ...
6294
6295
6296def to_identifier(name, quoted=None, copy=True):
6297    """Builds an identifier.
6298
6299    Args:
6300        name: The name to turn into an identifier.
6301        quoted: Whether to force quote the identifier.
6302        copy: Whether to copy name if it's an Identifier.
6303
6304    Returns:
6305        The identifier ast node.
6306    """
6307
6308    if name is None:
6309        return None
6310
6311    if isinstance(name, Identifier):
6312        identifier = maybe_copy(name, copy)
6313    elif isinstance(name, str):
6314        identifier = Identifier(
6315            this=name,
6316            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6317        )
6318    else:
6319        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6320    return identifier
6321
6322
6323def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6324    """
6325    Parses a given string into an identifier.
6326
6327    Args:
6328        name: The name to parse into an identifier.
6329        dialect: The dialect to parse against.
6330
6331    Returns:
6332        The identifier ast node.
6333    """
6334    try:
6335        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6336    except ParseError:
6337        expression = to_identifier(name)
6338
6339    return expression
6340
6341
6342INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6343
6344
6345def to_interval(interval: str | Literal) -> Interval:
6346    """Builds an interval expression from a string like '1 day' or '5 months'."""
6347    if isinstance(interval, Literal):
6348        if not interval.is_string:
6349            raise ValueError("Invalid interval string.")
6350
6351        interval = interval.this
6352
6353    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6354
6355    if not interval_parts:
6356        raise ValueError("Invalid interval string.")
6357
6358    return Interval(
6359        this=Literal.string(interval_parts.group(1)),
6360        unit=Var(this=interval_parts.group(2).upper()),
6361    )
6362
6363
6364@t.overload
6365def to_table(sql_path: str | Table, **kwargs) -> Table:
6366    ...
6367
6368
6369@t.overload
6370def to_table(sql_path: None, **kwargs) -> None:
6371    ...
6372
6373
6374def to_table(
6375    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6376) -> t.Optional[Table]:
6377    """
6378    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6379    If a table is passed in then that table is returned.
6380
6381    Args:
6382        sql_path: a `[catalog].[schema].[table]` string.
6383        dialect: the source dialect according to which the table name will be parsed.
6384        copy: Whether to copy a table if it is passed in.
6385        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6386
6387    Returns:
6388        A table expression.
6389    """
6390    if sql_path is None or isinstance(sql_path, Table):
6391        return maybe_copy(sql_path, copy=copy)
6392    if not isinstance(sql_path, str):
6393        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6394
6395    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6396    if table:
6397        for k, v in kwargs.items():
6398            table.set(k, v)
6399
6400    return table
6401
6402
6403def to_column(sql_path: str | Column, **kwargs) -> Column:
6404    """
6405    Create a column from a `[table].[column]` sql path. Schema is optional.
6406
6407    If a column is passed in then that column is returned.
6408
6409    Args:
6410        sql_path: `[table].[column]` string
6411    Returns:
6412        Table: A column expression
6413    """
6414    if sql_path is None or isinstance(sql_path, Column):
6415        return sql_path
6416    if not isinstance(sql_path, str):
6417        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6418    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6419
6420
6421def alias_(
6422    expression: ExpOrStr,
6423    alias: t.Optional[str | Identifier],
6424    table: bool | t.Sequence[str | Identifier] = False,
6425    quoted: t.Optional[bool] = None,
6426    dialect: DialectType = None,
6427    copy: bool = True,
6428    **opts,
6429):
6430    """Create an Alias expression.
6431
6432    Example:
6433        >>> alias_('foo', 'bar').sql()
6434        'foo AS bar'
6435
6436        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6437        '(SELECT 1, 2) AS bar(a, b)'
6438
6439    Args:
6440        expression: the SQL code strings to parse.
6441            If an Expression instance is passed, this is used as-is.
6442        alias: the alias name to use. If the name has
6443            special characters it is quoted.
6444        table: Whether to create a table alias, can also be a list of columns.
6445        quoted: whether to quote the alias
6446        dialect: the dialect used to parse the input expression.
6447        copy: Whether to copy the expression.
6448        **opts: other options to use to parse the input expressions.
6449
6450    Returns:
6451        Alias: the aliased expression
6452    """
6453    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6454    alias = to_identifier(alias, quoted=quoted)
6455
6456    if table:
6457        table_alias = TableAlias(this=alias)
6458        exp.set("alias", table_alias)
6459
6460        if not isinstance(table, bool):
6461            for column in table:
6462                table_alias.append("columns", to_identifier(column, quoted=quoted))
6463
6464        return exp
6465
6466    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6467    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6468    # for the complete Window expression.
6469    #
6470    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6471
6472    if "alias" in exp.arg_types and not isinstance(exp, Window):
6473        exp.set("alias", alias)
6474        return exp
6475    return Alias(this=exp, alias=alias)
6476
6477
6478def subquery(
6479    expression: ExpOrStr,
6480    alias: t.Optional[Identifier | str] = None,
6481    dialect: DialectType = None,
6482    **opts,
6483) -> Select:
6484    """
6485    Build a subquery expression.
6486
6487    Example:
6488        >>> subquery('select x from tbl', 'bar').select('x').sql()
6489        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6490
6491    Args:
6492        expression: the SQL code strings to parse.
6493            If an Expression instance is passed, this is used as-is.
6494        alias: the alias name to use.
6495        dialect: the dialect used to parse the input expression.
6496        **opts: other options to use to parse the input expressions.
6497
6498    Returns:
6499        A new Select instance with the subquery expression included.
6500    """
6501
6502    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6503    return Select().from_(expression, dialect=dialect, **opts)
6504
6505
6506@t.overload
6507def column(
6508    col: str | Identifier,
6509    table: t.Optional[str | Identifier] = None,
6510    db: t.Optional[str | Identifier] = None,
6511    catalog: t.Optional[str | Identifier] = None,
6512    *,
6513    fields: t.Collection[t.Union[str, Identifier]],
6514    quoted: t.Optional[bool] = None,
6515    copy: bool = True,
6516) -> Dot:
6517    pass
6518
6519
6520@t.overload
6521def column(
6522    col: str | Identifier,
6523    table: t.Optional[str | Identifier] = None,
6524    db: t.Optional[str | Identifier] = None,
6525    catalog: t.Optional[str | Identifier] = None,
6526    *,
6527    fields: Lit[None] = None,
6528    quoted: t.Optional[bool] = None,
6529    copy: bool = True,
6530) -> Column:
6531    pass
6532
6533
6534def column(
6535    col,
6536    table=None,
6537    db=None,
6538    catalog=None,
6539    *,
6540    fields=None,
6541    quoted=None,
6542    copy=True,
6543):
6544    """
6545    Build a Column.
6546
6547    Args:
6548        col: Column name.
6549        table: Table name.
6550        db: Database name.
6551        catalog: Catalog name.
6552        fields: Additional fields using dots.
6553        quoted: Whether to force quotes on the column's identifiers.
6554        copy: Whether to copy identifiers if passed in.
6555
6556    Returns:
6557        The new Column instance.
6558    """
6559    this = Column(
6560        this=to_identifier(col, quoted=quoted, copy=copy),
6561        table=to_identifier(table, quoted=quoted, copy=copy),
6562        db=to_identifier(db, quoted=quoted, copy=copy),
6563        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6564    )
6565
6566    if fields:
6567        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6568    return this
6569
6570
6571def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
6572    """Cast an expression to a data type.
6573
6574    Example:
6575        >>> cast('x + 1', 'int').sql()
6576        'CAST(x + 1 AS INT)'
6577
6578    Args:
6579        expression: The expression to cast.
6580        to: The datatype to cast to.
6581        copy: Whether to copy the supplied expressions.
6582
6583    Returns:
6584        The new Cast instance.
6585    """
6586    expression = maybe_parse(expression, copy=copy, **opts)
6587    data_type = DataType.build(to, copy=copy, **opts)
6588    expression = Cast(this=expression, to=data_type)
6589    expression.type = data_type
6590    return expression
6591
6592
6593def table_(
6594    table: Identifier | str,
6595    db: t.Optional[Identifier | str] = None,
6596    catalog: t.Optional[Identifier | str] = None,
6597    quoted: t.Optional[bool] = None,
6598    alias: t.Optional[Identifier | str] = None,
6599) -> Table:
6600    """Build a Table.
6601
6602    Args:
6603        table: Table name.
6604        db: Database name.
6605        catalog: Catalog name.
6606        quote: Whether to force quotes on the table's identifiers.
6607        alias: Table's alias.
6608
6609    Returns:
6610        The new Table instance.
6611    """
6612    return Table(
6613        this=to_identifier(table, quoted=quoted) if table else None,
6614        db=to_identifier(db, quoted=quoted) if db else None,
6615        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6616        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6617    )
6618
6619
6620def values(
6621    values: t.Iterable[t.Tuple[t.Any, ...]],
6622    alias: t.Optional[str] = None,
6623    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6624) -> Values:
6625    """Build VALUES statement.
6626
6627    Example:
6628        >>> values([(1, '2')]).sql()
6629        "VALUES (1, '2')"
6630
6631    Args:
6632        values: values statements that will be converted to SQL
6633        alias: optional alias
6634        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6635         If either are provided then an alias is also required.
6636
6637    Returns:
6638        Values: the Values expression object
6639    """
6640    if columns and not alias:
6641        raise ValueError("Alias is required when providing columns")
6642
6643    return Values(
6644        expressions=[convert(tup) for tup in values],
6645        alias=(
6646            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6647            if columns
6648            else (TableAlias(this=to_identifier(alias)) if alias else None)
6649        ),
6650    )
6651
6652
6653def var(name: t.Optional[ExpOrStr]) -> Var:
6654    """Build a SQL variable.
6655
6656    Example:
6657        >>> repr(var('x'))
6658        'Var(this=x)'
6659
6660        >>> repr(var(column('x', table='y')))
6661        'Var(this=x)'
6662
6663    Args:
6664        name: The name of the var or an expression who's name will become the var.
6665
6666    Returns:
6667        The new variable node.
6668    """
6669    if not name:
6670        raise ValueError("Cannot convert empty name into var.")
6671
6672    if isinstance(name, Expression):
6673        name = name.name
6674    return Var(this=name)
6675
6676
6677def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6678    """Build ALTER TABLE... RENAME... expression
6679
6680    Args:
6681        old_name: The old name of the table
6682        new_name: The new name of the table
6683
6684    Returns:
6685        Alter table expression
6686    """
6687    old_table = to_table(old_name)
6688    new_table = to_table(new_name)
6689    return AlterTable(
6690        this=old_table,
6691        actions=[
6692            RenameTable(this=new_table),
6693        ],
6694    )
6695
6696
6697def rename_column(
6698    table_name: str | Table,
6699    old_column_name: str | Column,
6700    new_column_name: str | Column,
6701    exists: t.Optional[bool] = None,
6702) -> AlterTable:
6703    """Build ALTER TABLE... RENAME COLUMN... expression
6704
6705    Args:
6706        table_name: Name of the table
6707        old_column: The old name of the column
6708        new_column: The new name of the column
6709        exists: Whether to add the `IF EXISTS` clause
6710
6711    Returns:
6712        Alter table expression
6713    """
6714    table = to_table(table_name)
6715    old_column = to_column(old_column_name)
6716    new_column = to_column(new_column_name)
6717    return AlterTable(
6718        this=table,
6719        actions=[
6720            RenameColumn(this=old_column, to=new_column, exists=exists),
6721        ],
6722    )
6723
6724
6725def convert(value: t.Any, copy: bool = False) -> Expression:
6726    """Convert a python value into an expression object.
6727
6728    Raises an error if a conversion is not possible.
6729
6730    Args:
6731        value: A python object.
6732        copy: Whether to copy `value` (only applies to Expressions and collections).
6733
6734    Returns:
6735        Expression: the equivalent expression object.
6736    """
6737    if isinstance(value, Expression):
6738        return maybe_copy(value, copy)
6739    if isinstance(value, str):
6740        return Literal.string(value)
6741    if isinstance(value, bool):
6742        return Boolean(this=value)
6743    if value is None or (isinstance(value, float) and math.isnan(value)):
6744        return null()
6745    if isinstance(value, numbers.Number):
6746        return Literal.number(value)
6747    if isinstance(value, datetime.datetime):
6748        datetime_literal = Literal.string(
6749            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6750        )
6751        return TimeStrToTime(this=datetime_literal)
6752    if isinstance(value, datetime.date):
6753        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6754        return DateStrToDate(this=date_literal)
6755    if isinstance(value, tuple):
6756        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6757    if isinstance(value, list):
6758        return Array(expressions=[convert(v, copy=copy) for v in value])
6759    if isinstance(value, dict):
6760        return Map(
6761            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6762            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6763        )
6764    raise ValueError(f"Cannot convert {value}")
6765
6766
6767def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6768    """
6769    Replace children of an expression with the result of a lambda fun(child) -> exp.
6770    """
6771    for k, v in expression.args.items():
6772        is_list_arg = type(v) is list
6773
6774        child_nodes = v if is_list_arg else [v]
6775        new_child_nodes = []
6776
6777        for cn in child_nodes:
6778            if isinstance(cn, Expression):
6779                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6780                    new_child_nodes.append(child_node)
6781                    child_node.parent = expression
6782                    child_node.arg_key = k
6783            else:
6784                new_child_nodes.append(cn)
6785
6786        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6787
6788
6789def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6790    """
6791    Return all table names referenced through columns in an expression.
6792
6793    Example:
6794        >>> import sqlglot
6795        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6796        ['a', 'c']
6797
6798    Args:
6799        expression: expression to find table names.
6800        exclude: a table name to exclude
6801
6802    Returns:
6803        A list of unique names.
6804    """
6805    return {
6806        table
6807        for table in (column.table for column in expression.find_all(Column))
6808        if table and table != exclude
6809    }
6810
6811
6812def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6813    """Get the full name of a table as a string.
6814
6815    Args:
6816        table: Table expression node or string.
6817        dialect: The dialect to generate the table name for.
6818        identify: Determines when an identifier should be quoted. Possible values are:
6819            False (default): Never quote, except in cases where it's mandatory by the dialect.
6820            True: Always quote.
6821
6822    Examples:
6823        >>> from sqlglot import exp, parse_one
6824        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6825        'a.b.c'
6826
6827    Returns:
6828        The table name.
6829    """
6830
6831    table = maybe_parse(table, into=Table, dialect=dialect)
6832
6833    if not table:
6834        raise ValueError(f"Cannot parse {table}")
6835
6836    return ".".join(
6837        (
6838            part.sql(dialect=dialect, identify=True, copy=False)
6839            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6840            else part.name
6841        )
6842        for part in table.parts
6843    )
6844
6845
6846def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6847    """Returns a case normalized table name without quotes.
6848
6849    Args:
6850        table: the table to normalize
6851        dialect: the dialect to use for normalization rules
6852        copy: whether to copy the expression.
6853
6854    Examples:
6855        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6856        'A-B.c'
6857    """
6858    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6859
6860    return ".".join(
6861        p.name
6862        for p in normalize_identifiers(
6863            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6864        ).parts
6865    )
6866
6867
6868def replace_tables(
6869    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6870) -> E:
6871    """Replace all tables in expression according to the mapping.
6872
6873    Args:
6874        expression: expression node to be transformed and replaced.
6875        mapping: mapping of table names.
6876        dialect: the dialect of the mapping table
6877        copy: whether to copy the expression.
6878
6879    Examples:
6880        >>> from sqlglot import exp, parse_one
6881        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6882        'SELECT * FROM c /* a.b */'
6883
6884    Returns:
6885        The mapped expression.
6886    """
6887
6888    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6889
6890    def _replace_tables(node: Expression) -> Expression:
6891        if isinstance(node, Table):
6892            original = normalize_table_name(node, dialect=dialect)
6893            new_name = mapping.get(original)
6894
6895            if new_name:
6896                table = to_table(
6897                    new_name,
6898                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6899                    dialect=dialect,
6900                )
6901                table.add_comments([original])
6902                return table
6903        return node
6904
6905    return expression.transform(_replace_tables, copy=copy)
6906
6907
6908def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6909    """Replace placeholders in an expression.
6910
6911    Args:
6912        expression: expression node to be transformed and replaced.
6913        args: positional names that will substitute unnamed placeholders in the given order.
6914        kwargs: keyword arguments that will substitute named placeholders.
6915
6916    Examples:
6917        >>> from sqlglot import exp, parse_one
6918        >>> replace_placeholders(
6919        ...     parse_one("select * from :tbl where ? = ?"),
6920        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6921        ... ).sql()
6922        "SELECT * FROM foo WHERE str_col = 'b'"
6923
6924    Returns:
6925        The mapped expression.
6926    """
6927
6928    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6929        if isinstance(node, Placeholder):
6930            if node.name:
6931                new_name = kwargs.get(node.name)
6932                if new_name:
6933                    return convert(new_name)
6934            else:
6935                try:
6936                    return convert(next(args))
6937                except StopIteration:
6938                    pass
6939        return node
6940
6941    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6942
6943
6944def expand(
6945    expression: Expression,
6946    sources: t.Dict[str, Subqueryable],
6947    dialect: DialectType = None,
6948    copy: bool = True,
6949) -> Expression:
6950    """Transforms an expression by expanding all referenced sources into subqueries.
6951
6952    Examples:
6953        >>> from sqlglot import parse_one
6954        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6955        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6956
6957        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6958        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6959
6960    Args:
6961        expression: The expression to expand.
6962        sources: A dictionary of name to Subqueryables.
6963        dialect: The dialect of the sources dict.
6964        copy: Whether to copy the expression during transformation. Defaults to True.
6965
6966    Returns:
6967        The transformed expression.
6968    """
6969    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
6970
6971    def _expand(node: Expression):
6972        if isinstance(node, Table):
6973            name = normalize_table_name(node, dialect=dialect)
6974            source = sources.get(name)
6975            if source:
6976                subquery = source.subquery(node.alias or name)
6977                subquery.comments = [f"source: {name}"]
6978                return subquery.transform(_expand, copy=False)
6979        return node
6980
6981    return expression.transform(_expand, copy=copy)
6982
6983
6984def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
6985    """
6986    Returns a Func expression.
6987
6988    Examples:
6989        >>> func("abs", 5).sql()
6990        'ABS(5)'
6991
6992        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6993        'CAST(5 AS DOUBLE)'
6994
6995    Args:
6996        name: the name of the function to build.
6997        args: the args used to instantiate the function of interest.
6998        copy: whether to copy the argument expressions.
6999        dialect: the source dialect.
7000        kwargs: the kwargs used to instantiate the function of interest.
7001
7002    Note:
7003        The arguments `args` and `kwargs` are mutually exclusive.
7004
7005    Returns:
7006        An instance of the function of interest, or an anonymous function, if `name` doesn't
7007        correspond to an existing `sqlglot.expressions.Func` class.
7008    """
7009    if args and kwargs:
7010        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7011
7012    from sqlglot.dialects.dialect import Dialect
7013
7014    dialect = Dialect.get_or_raise(dialect)
7015
7016    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7017    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7018
7019    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7020    if constructor:
7021        if converted:
7022            if "dialect" in constructor.__code__.co_varnames:
7023                function = constructor(converted, dialect=dialect)
7024            else:
7025                function = constructor(converted)
7026        elif constructor.__name__ == "from_arg_list":
7027            function = constructor.__self__(**kwargs)  # type: ignore
7028        else:
7029            constructor = FUNCTION_BY_NAME.get(name.upper())
7030            if constructor:
7031                function = constructor(**kwargs)
7032            else:
7033                raise ValueError(
7034                    f"Unable to convert '{name}' into a Func. Either manually construct "
7035                    "the Func expression of interest or parse the function call."
7036                )
7037    else:
7038        kwargs = kwargs or {"expressions": converted}
7039        function = Anonymous(this=name, **kwargs)
7040
7041    for error_message in function.error_messages(converted):
7042        raise ValueError(error_message)
7043
7044    return function
7045
7046
7047def case(
7048    expression: t.Optional[ExpOrStr] = None,
7049    **opts,
7050) -> Case:
7051    """
7052    Initialize a CASE statement.
7053
7054    Example:
7055        case().when("a = 1", "foo").else_("bar")
7056
7057    Args:
7058        expression: Optionally, the input expression (not all dialects support this)
7059        **opts: Extra keyword arguments for parsing `expression`
7060    """
7061    if expression is not None:
7062        this = maybe_parse(expression, **opts)
7063    else:
7064        this = None
7065    return Case(this=this, ifs=[])
7066
7067
7068def cast_unless(
7069    expression: ExpOrStr,
7070    to: DATA_TYPE,
7071    *types: DATA_TYPE,
7072    **opts: t.Any,
7073) -> Expression | Cast:
7074    """
7075    Cast an expression to a data type unless it is a specified type.
7076
7077    Args:
7078        expression: The expression to cast.
7079        to: The data type to cast to.
7080        **types: The types to exclude from casting.
7081        **opts: Extra keyword arguments for parsing `expression`
7082    """
7083    expr = maybe_parse(expression, **opts)
7084    if expr.is_type(*types):
7085        return expr
7086    return cast(expr, to, **opts)
7087
7088
7089def array(
7090    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7091) -> Array:
7092    """
7093    Returns an array.
7094
7095    Examples:
7096        >>> array(1, 'x').sql()
7097        'ARRAY(1, x)'
7098
7099    Args:
7100        expressions: the expressions to add to the array.
7101        copy: whether to copy the argument expressions.
7102        dialect: the source dialect.
7103        kwargs: the kwargs used to instantiate the function of interest.
7104
7105    Returns:
7106        An array expression.
7107    """
7108    return Array(
7109        expressions=[
7110            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7111            for expression in expressions
7112        ]
7113    )
7114
7115
7116def tuple_(
7117    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7118) -> Tuple:
7119    """
7120    Returns an tuple.
7121
7122    Examples:
7123        >>> tuple_(1, 'x').sql()
7124        '(1, x)'
7125
7126    Args:
7127        expressions: the expressions to add to the tuple.
7128        copy: whether to copy the argument expressions.
7129        dialect: the source dialect.
7130        kwargs: the kwargs used to instantiate the function of interest.
7131
7132    Returns:
7133        A tuple expression.
7134    """
7135    return Tuple(
7136        expressions=[
7137            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7138            for expression in expressions
7139        ]
7140    )
7141
7142
7143def true() -> Boolean:
7144    """
7145    Returns a true Boolean expression.
7146    """
7147    return Boolean(this=True)
7148
7149
7150def false() -> Boolean:
7151    """
7152    Returns a false Boolean expression.
7153    """
7154    return Boolean(this=False)
7155
7156
7157def null() -> Null:
7158    """
7159    Returns a Null expression.
7160    """
7161    return Null()